1

I am looking for a query to find nth value in a list. The separator is anything greater than or equal to 2 spaces. (it can be 3, or 5 spaces). Trying to avoid scalar value functions, since performance may be slower. The sentences can have any number of words, from 5-20.

CREATE TABLE dbo.TestWrite (TestWriteId int primary key identity(1,1), 
                            TextRow varchar(255))
INSERT INTO dbo.TestWrite (TextRow)
SELECT 'I am writing SQL Code.'
UNION ALL
SELECT 'SQL keywords include join, except, where.'


+-----+----------+---------+---------------+---------+----------+
| SQL | keywords | include |     join,     | except, |   where. |
+-----+----------+---------+---------------+---------+----------+
| I   | am       | writing |    SQL  Code. |         |          |
+-----+----------+---------+---------------+---------+----------+

Would like in individual rows with columns, see comments above.

This may be one solution trying to utilize. https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string

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)')
  • I've got the strangest feeling that I saw this question earlier today. But now I can't find it. Did you perhaps cross-post this to other sites? – Doug Deden Mar 1 at 0:16
1

Splitting to separate fields of a result set is a bit tricky if you have a varying number of elements per row. There is a SQLCLR stored procedure in the SQL# library (that I wrote) that, while not in the Free version, does break a delimited string of varying elements into result set fields based on a Regular Expression delimiter. The delimiter being RegEx makes it quite easy to treat "one or more spaces" as the delimiter: \s+. For example:

EXEC SQL#.String_SplitResultIntoFields N'
SELECT [TextRow] FROM #TestWrite;',
N'\s+', NULL, NULL;
/*
Field1    Field2     Field3     Field4    Field5
I         am         writing    SQL       Code.
SQL       keywords   include    join,     except,
*/

As you can see, it determines the number of fields for the result set based on the first row, which is why there is no "Field6" to contain the final word in the second row.

Of course, you could always seed the first row with dashes separated by spaces to force a certain number of fields, but there is no way to then filter out that initial row:

EXEC SQL#.String_SplitResultIntoFields N'
SELECT N''- - - - - - - -''
UNION ALL
SELECT [TextRow] FROM #TestWrite;',
N'\s+', NULL, NULL;
/*
Field1    Field2     Field3     Field4    Field5    Field6   Field7    Field8
-         -          -          -         -         -        -         -
I         am         writing    SQL       Code.
SQL       keywords   include    join,     except,   where.
*/

I suppose I can add an optional input parameter for @ForceResultSetFieldCount easily enough, but it's not there as of today.

If this request was only what is initially stated in the question (i.e. "I am looking for a query to find nth value in a list"), then that is not only trivial (even with the "one or more spaces" delimiter requirement), but that requires a regular expression function, RegEx_CaptureGroupCapture, and that is in the Free version of SQL#. For example:

SETUP

CREATE TABLE #TestWrite
(
  TestWriteId INT PRIMARY KEY IDENTITY(1, 1),
  TextRow VARCHAR(255)
);

INSERT INTO #TestWrite (TextRow)
  SELECT 'I am     writing SQL                   Code.'
  UNION ALL
  SELECT 'SQL  keywords include   join,    except, where.';

TESTS

As you can see below, you can either use a pattern of "one or more word characters", which will exclude both whitespace and punctuation (first example), or you can use a pattern of "one or more non-whitespace", which will include punctuation, etc (second example).

-- only get "word" characters:
SELECT SQL#.RegEx_CaptureGroupCapture(t.[TextRow], N'(\w+)', 1, 4, 1, NULL, 1, -1, NULL)
FROM   #TestWrite t;
/*
SQL
join
*/

-- get non-whitespace:
SELECT SQL#.RegEx_CaptureGroupCapture(t.[TextRow], N'([^\s]+)', 1, 4, 1, NULL, 1, -1, NULL)
FROM   #TestWrite t;
/*
SQL
join,
*/
0

Next is one possible approach, using OPENJSON. The important part here is the fact, that when OPENJSON parses a JSON array the indexes of the elements in the JSON text are returned as keys (0-based).

Table:

CREATE TABLE #TestWrite (
   TestWriteId int primary key identity(1,1), 
   TextRow varchar(255)
)
INSERT INTO #TestWrite
   (TextRow)
VALUES
   ('I    am    writing       SQL  Code.'),
   ('SQL   keywords    include'),
   ('One space as first delimiter,  then  more spaces are used.')

Statement:

DECLARE @dlmt varchar(10) = '  ';
DECLARE @pos int = 2;

WITH cte AS (
   SELECT 
      t.TestWriteId,
      t.[TextRow],
      ROW_NUMBER() OVER (PARTITION BY t.TestWriteId ORDER BY CONVERT(int, j.[key])) AS Rn,
      j.[key],
      j.[word]
   FROM #TestWrite t
   CROSS APPLY (
      SELECT [key], LTRIM(RTRIM([value])) AS [word]
      FROM OPENJSON('["' +  REPLACE(t.TextRow, @dlmt, '","') + '"]')
      WHERE LTRIM(RTRIM([value])) <> ''
   ) j
)
SELECT
   TestWriteId,
   TextRow,
   word
FROM cte
WHERE Rn = @pos

Output:

TestWriteId TextRow                                                     word
1           I    am    writing       SQL  Code.                         am
2           SQL   keywords    include                                   keywords
3           One space as first delimiter,  then  more spaces are used.  then

Notes:

STRING_SPLIT is an option here, but the order of splitted elements is not guaranteed and selecting Nth in this case may not work as expected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy