Using T-SQL, return nth delimited element from a string

by

[*]

This is the easiest answer to rerieve the 67 (type-safe!!):

SELECT CAST('<x>' + REPLACE('1,222,2,67,888,1111',',','</x><x>') + '</x>' AS XML).value('/x[4]','int')

In the following you will find examples how to use this with variables for the string, the delimiter and the position (even for edge-cases with XML-forbidden characters)

The easy one

This question is not about a string split approach, but about how to get the nth element. The easiest, fully inlineable way would be this IMO:

This is a real one-liner to get part 2 delimited by a space:

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

Variables can be used with sql:variable() or sql:column()

Of course you can use variables for delimiter and position (use sql:column to retrieve the position directly from a query’s value):

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

Edge-Case with XML-forbidden characters

If your string might include forbidden characters, you still can do it this way. Just use FOR XML PATH on your string first to replace all forbidden characters with the fitting escape sequence implicitly.

It’s a very special case if – additionally – your delimiter is the semicolon. In this case I replace the delimiter first to ‘#DLMT#’, and replace this to the XML tags finally:

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

UPDATE for SQL-Server 2016+

Regretfully the developers forgot to return the part’s index with STRING_SPLIT. But, using SQL-Server 2016+, there is JSON_VALUE and OPENJSON.

With JSON_VALUE we can pass in the position as the index’ array.

For OPENJSON the documentation states clearly:

When OPENJSON parses a JSON array, the function returns the indexes of the elements in the JSON text as keys.

A string like 1,2,3 needs nothing more than brackets: [1,2,3].
A string of words like this is an example needs to be ["this","is","an"," example"].
These are very easy string operations. Just try it out:

DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;

--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

–See this for a position safe string-splitter (zero-based):

SELECT  JsonArray.[key] AS [Position]
       ,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

In this post I tested various approaches and found, that OPENJSON is really fast. Even much faster than the famous “delimitedSplit8k()” method…

UPDATE 2 – Get the values type-safe

We can use an array within an array simply by using doubled [[]]. This allows for a typed WITH-clause:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment VARCHAR(100) '$[0]'
    ,TheSecondFragment INT '$[1]'
    ,TheThirdFragment DATE '$[2]') ValuesFromTheArray

More Related Contents:

  1. Turning a Comma Separated string into individual rows
  2. T-SQL split string
  3. Comma separated results in SQL
  4. How to check if a column exists in a SQL Server table
  5. Understanding PIVOT function in T-SQL
  6. Get first day of week in SQL Server
  7. Using RegEx in SQL Server
  8. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  9. Confused about UPDLOCK, HOLDLOCK
  10. INSERT INTO vs SELECT INTO
  11. SQL Server 2008 Empty String vs. Space
  12. How do I use the CONCAT function in SQL Server 2008 R2?
  13. How to set a default value for an existing column
  14. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  15. TSQL: How to convert local time to UTC? (SQL Server 2008)
  16. SQL Server: combining multiple rows into one row
  17. How do I find a stored procedure containing ?
  18. SQL Recursive CTE: Finding objects linked by property
  19. How to convert number of minutes to hh:mm format in TSQL?
  20. Convert decimal time to hours and minutes
  21. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  22. How do I move a table into a schema in T-SQL
  23. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  24. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  25. How to wait for 2 seconds?
  26. What are row, page and table locks? And when they are acquired?
  27. Generate MD5 hash string with T-SQL
  28. An attempt to login using SQL authentication failed
  29. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  30. Does query plan optimizer works well with joined/filtered table-valued functions?

Leave a Comment