SQL Split Row Data Separated by Spaces

Question

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)')

score 1 · Answer 1 · answered May 01 '19 at 15:54

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,
*/

Zhorov · Answer 2 · 2020-04-13T05:54:49.133

One possible approach to get the expected results is based on JSON. It's necessary to transform the input data into a valid JSON array (I am writing SQL Code. is transformed into ["I","am","writing","SQL","Code."] ) and parse this array with 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). Note, that STRING_SPLIT() is not an option here, because, as is mentioned in the documentation, the output rows might be in any order and the order is not guaranteed to match the order of the substrings in the input string.

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

SQL Split Row Data Separated by Spaces

2 Answers2