CODEWITHSUNDEEP
sql-serversubstringcharindex
Shodhan Pujari
Shodhan Pujari

Reputation: 33

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

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.

Upvotes: 2

Views: 1057

Answers (2)

LukStorms
LukStorms

Reputation: 29647

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

Upvotes: 1

Chad Estes
Chad Estes

Reputation: 381

Not the most elegant, but solves for the given requirements:

DECLARE @InputValue AS VARCHAR(200) = '100231|ABC Limited||Liquidated|514321||AU||Testwood|5165|5/14/1996 12:00:00 AM|8/1/2003 12:00:00 AM|Test1|Test2|'

SELECT
SUBSTRING(@InputValue,
    CHARINDEX('|',@InputValue,
        CHARINDEX('|',@InputValue,
            CHARINDEX('|',@InputValue,
                CHARINDEX('|',@InputValue,
                    CHARINDEX('|',@InputValue,
                        CHARINDEX('|',@InputValue,
                            CHARINDEX('|',@InputValue,
                                CHARINDEX('|',@InputValue,
                                    CHARINDEX('|',@InputValue,
                                        CHARINDEX('|',@InputValue,
                                            CHARINDEX('|',@InputValue,
                                                CHARINDEX('|',@InputValue) + 1
                                            ) + 1
                                        ) + 1
                                    ) + 1
                                ) + 1
                            ) + 1
                        ) + 1 
                    ) + 1
                ) + 1
            ) + 1
        ) + 1
    )
,LEN(@InputValue))

Assuming that you know there will always be 14 '|' per string you could use a more abbreviated version such as: 

DECLARE @InputValue AS VARCHAR(200) = '100231|ABC Limited||Liquidated|514321||AU||Testwood|5165|5/14/1996 12:00:00 AM|8/1/2003 12:00:00 AM|Test1|Test2|'

SELECT
RIGHT(@InputValue,
    CHARINDEX('|',REVERSE(@InputValue),
        CHARINDEX('|',REVERSE(@InputValue),
            CHARINDEX('|',REVERSE(@InputValue)) + 1
        ) + 1
    )
)

Upvotes: 1

Related Questions