Extract all the characters after a position or index value in SQL Server

Question

The below is a sample data

100231|ABC Limited||Liquidated|514321||AU||Testwood|5165|5/14/1996 12:00:00 AM|8/1/2003 12:00:00 AM|Test1|Test2|

I want to extract all the data after position 12 of '|' pipe delimiter using SQL Server functions i.e. the output should be |Test1|Test2|

I have tried using the following:

Select RIGHT(@InputValue,CHARINDEX('|',REVERSE(@InputValue))-1)

But it doesn't give the required output.

Answer 1

You can also use a recursive CTE for this.

Let it calculate the positions of the delimiters.
Then select a substring based on the calculated positions.

WITH CTE AS
(
  SELECT 
  1 as FieldNr,
  -- LEFT(@InputValue,CHARINDEX('|', @InputValue)-1) AS Field,
  0 as Pos1, 
  CHARINDEX('|',@InputValue) as Pos2

  UNION ALL

  SELECT 
  FieldNr+1,
  -- SUBSTRING(@InputValue, Pos2+1, CHARINDEX('|',@InputValue, Pos2+1)-Pos2-1), 
  Pos2, 
  CHARINDEX('|',@InputValue, Pos2+1)
 FROM CTE
 WHERE Pos2 > 0 AND Pos2 < LEN(@InputValue)
)
SELECT SUBSTRING(@InputValue, MIN(Pos1)+1, MAX(Pos2)-MIN(Pos1)) AS AfterFieldNr
FROM CTE
WHERE FieldNr > 12;

Returns:

Test1|Test2|

Test here on rextester.

But if you have SQL Server 2017 or later, then you could use STRING_SPLIT and STRING_AGG.

SELECT STRING_AGG(value,'|') AS After12
FROM
(
  SELECT
   value,
   ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn
  FROM STRING_SPLIT(@InputValue, '|')
) AS q
WHERE rn > 12;

db<>fiddle here