T-SQL Find char string and take all char to right

Question

How do I

Take:

RJI#\\\\Cjserver\TrialWorks\CaseFiles\10000269\Pleadings\RJI - 10005781.doc

Find Constant expression '\\Cjserver\' and take everything to the right of the expression so the correct pointer would be:

\\\\Cjserver\TrialWorks\CaseFiles\10000269\Pleadings\RJI - 10005781.doc

I know some kind of combinaton of RIGHT and CHARINDEX should do it.

Answer 1

DECLARE @String VARCHAR(100)
SET @String = 'RJI#\\Cjserver\TrialWorks\CaseFiles\10000269\Pleadings\RJI - 10005781.doc'

SELECT RIGHT(@String,LEN(@String)-PATINDEX('%\\Cjserver\%',@String)+1)

Answer 2

DECLARE @input NVarChar(1000) =
  'RJI#\\Cjserver\TrialWorks\CaseFiles\10000269\Pleadings\RJI - 10005781.doc',
        @match NVarChar(100) =
  '\\Cjserver';
DECLARE @position Int = CHARINDEX(@match, @input);

SELECT SUBSTRING(@input, @position, 1000);

I'm just using 1000 for some arbitrarily large value. You should probably size this more appropriately to your data.

Answer 3

You want to use Substring, starting one after the index of your target, and take the length of the entire string less the charindex of your target

  declare @string varchar(1000)
     set @string = 'xxxxxxxxyzzzzzzzz'
     select substring(@string, charindex('y', @string) +1, 
     len(@string) - charindex('y', @string))
     zzzzzzzz

In this case I want everything after the y