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)

I    am    writing       SQL  Code.                          -- Row 1: Column 1: I ,    Column 2: am,       Column 3: writing...
SQL   keywords    include       join,   except,      where.  -- Row 2: Column 1: SQL,   Column 2: keywords, Column 3: include...

Would like in individual rows with columns, see expected output -- comments above, using a select statement.

This maybe one solution trying to utilize. 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)')

@DarkRob, the expected output is in the comments beside the row data... — Shnugo, Mar 01 '19 at 09:37

score 2 · Accepted Answer · answered Mar 01 '19 at 09:36

Your problem is the fact, that splitting the string by blanks leads to a lot of empty elements. Picking the 2nd fragment has a high chance to hit such an empty one.

There are two approaches:

Remove all multiple spaces to return your strings with on single blank as delimiter. Then use the approach you've found in the other question.
Use XQuery to return only elements with a text() node.

Try this out:

DECLARE @TestWrite TABLE(TestWriteId int primary key identity(1,1), TextRow varchar(255))
insert into @TestWrite (TextRow) values
 ('I    am    writing       SQL  Code.')                           -- Row 1: Column 1: I ,    Column 2: am,       Column 3: writing...
,('SQL   keywords    include       join,   except,      where.');  -- Row 2: Column 1: SQL,   Column 2: keywords, Column 3: include...

ad 1. : Use any two characters, which will not appear in your strings

SELECT REPLACE(REPLACE(REPLACE(TextRow,' ','<>'),'><',''),'<>',' ')
FROM @TestWrite;

ad 2.

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;

SELECT CAST(N'<x>' + REPLACE(t.TextRow,@dlmt,N'</x><x>') + N'</x>' AS XML)
      .query('/x[text()]')  --<-- Here's the magic
      .value('/x[sql:variable("@pos")][1]','nvarchar(max)')
FROM @TestWrite t

SQL Split Row Data Separated by Spaces

1 Answers1