T-SQL to get the 5th word in each row in a database

Question

I need to find out how to code T-SQL that gets the 5th word in each row in a database

Answer 1

One option which will ensure sequence is to use a bit of XML

Example

Declare @YourTable table (ID int, SomeCol varchar(100))
Insert into @YourTable Values
 (1,'Some text that needs to be parsed')
,(2,'Only three words')

Select A.ID
      ,Pos5  =  cast('<x>' + replace(SomeCol,' ','</x><x>')+'</x>' as xml).value('/x[5]','varchar(50)')
 From  @YourTable A

Returns

ID  Pos5
1   to
2   NULL

Answer 2

If you are on v2016 or higher there is a trick with JSON, which is faster than XML.

Credits to John Cappelletti for the DDL/INSERT)

Declare @YourTable table (ID int, SomeCol varchar(100))
Insert into @YourTable Values
 (1,'Some text that needs to be parsed')
,(2,'Only three words');

SELECT JSON_VALUE(CONCAT('["',REPLACE(t.SomeCol,' ','","'),'"]'),'$[4]')
FROM @YourTable t

The idea is to transform the string into a JSON array an pick the item by its (zero-based!) position.

UPDATE Performance

There are three different answers now... Here is a comparison of their performance:

SET NOCOUNT ON;

DECLARE @YourTable TABLE (ID INT IDENTITY, SomeCol VARCHAR(100))
DECLARE @cnt INT=0;

--insert 100k rows
--important: each string must be different to avoid biased results due to caching...
WHILE @cnt<100000
BEGIN
    INSERT INTO @YourTable SELECT CONCAT('Some text that needs to', @cnt,' be parsed');
    SET @cnt += 1;
END

--we will measure each approach's duration
DECLARE @d DATETIME2=SYSUTCDATETIME();

--JSON_VALUE reads from the parsed value directly
SELECT t.ID,JSON_VALUE(CONCAT('["',REPLACE(t.SomeCol,' ','","'),'"]'),'$[4]') AS fifth
INTO #tbl1
FROM @YourTable t;

SELECT DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());

SET @d=SYSUTCDATETIME();

--In this approach we use OPENJSON and implicit pivoting with a WITH clause
SELECT t.ID,f.fifth
INTO #tbl2
FROM @YourTable t
CROSS APPLY OPENJSON(CONCAT('[["',REPLACE(SomeCol,' ','","'),'"]]')) 
WITH (fifth VARCHAR(255) '$[4]') as f

SELECT DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());

SET @d=SYSUTCDATETIME();

--In this approach we use XML to get the 5th element
SELECT t.ID,cast('<x>' + replace(SomeCol,' ','</x><x>')+'</x>' as xml).value('/x[5]','varchar(50)') AS fifth
INTO #tbl3
FROM @YourTable t

SELECT DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
GO

--You can check the result
SELECT TOP 100 * FROM #tbl1; 
SELECT TOP 100 * FROM #tbl2; 
SELECT TOP 100 * FROM #tbl3; 
GO
--cleanup

DROP TABLE #tbl1;
GO
DROP TABLE #tbl2;
GO
DROP TABLE #tbl3;
GO

On my system JSON_VALUE (~450ms) is about half of OPENJSON(~900ms) and a 10th(!!! ~4800ms) of XML.

UPDATE 2

Reading more than one vlaue from the string in one go tends to favour the solution with OPENJSON:

DECLARE @d DATETIME2=SYSUTCDATETIME();

SELECT t.ID,JSON_VALUE(CONCAT('["',REPLACE(t.SomeCol,' ','","'),'"]'),'$[4]') AS fifth
           ,JSON_VALUE(CONCAT('["',REPLACE(t.SomeCol,' ','","'),'"]'),'$[1]') AS [second]
           ,JSON_VALUE(CONCAT('["',REPLACE(t.SomeCol,' ','","'),'"]'),'$[0]') AS [first]
INTO #tbl1
FROM @YourTable t;

SELECT DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());

SET @d=SYSUTCDATETIME();

SELECT t.ID,f.fifth,f.[second],f.[first]
INTO #tbl2
FROM @YourTable t
CROSS APPLY OPENJSON(CONCAT('[["',REPLACE(SomeCol,' ','","'),'"]]')) 
WITH (fifth VARCHAR(255) '$[4]'
     ,[second] VARCHAR(255) '$[1]'
     ,[first] VARCHAR(255) '$[0]') as f

SELECT DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());

SET @d=SYSUTCDATETIME();

SELECT t.ID,cast('<x>' + replace(SomeCol,' ','</x><x>')+'</x>' as xml).value('/x[5]','varchar(50)') AS fifth
           ,cast('<x>' + replace(SomeCol,' ','</x><x>')+'</x>' as xml).value('/x[2]','varchar(50)') AS [second]
           ,cast('<x>' + replace(SomeCol,' ','</x><x>')+'</x>' as xml).value('/x[1]','varchar(50)') AS [first]
INTO #tbl3
FROM @YourTable t

SELECT DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
GO

In this case (reading 3 items at once) I get ~1800ms for JSON_VALUE, just ~1100ms for OPENJSON and - again about 10x - 18200ms for the XML approach.

UPDATE 3 - just for fun ;-)

The test in section UPDATE 2 would work better for JSON_VALUE using this:

SELECT t.ID,JSON_VALUE(TheJsonString,'$[4]') AS fifth
           ,JSON_VALUE(TheJsonString,'$[1]') AS [second]
           ,JSON_VALUE(TheJsonString,'$[0]') AS [first]
INTO #tbl1
FROM @YourTable t
CROSS APPLY(VALUES(CONCAT('["',REPLACE(t.SomeCol,' ','","'),'"]')))A(TheJsonString);

... thus avoiding repeated string manipulations... In this case it is at the same speed as OPENJSON.

Conclusio

So the final suggestion: Use JSON_VALUE for a single item and OPENJSON for more than one item and XML if you are below v2016.

Answer 3

This is a good question, and requires and highlights a couple of useful features of SQL.

It can be done using a combination of CTE, STRING_SPLIT and CROSS APPLY.

First create the table and insert a couple of rows of data:

create table mywords(r int, w varchar(100));
insert mywords values 
   (1, 'one two three four five six seven')
  ,(2, 'W1 W2 W3 W4 W5 W6 W7');

Then the query

;with words as 
(
SELECT r, 
    row_number() over (partition by r order by w) as n, 
    Value 
FROM mywords 
    CROSS APPLY STRING_SPLIT(w, ' ')
)
select * from words where n=5

The result is:

r   n   Value
1   5   five
2   5   W5

Answer 4

You can get it using OPENJSON. Thanks to @TomC for create script.

create table #mywords(r int, w varchar(100));
insert #mywords values 
   (1, 'one two three four five six seven')
  ,(2, 'W1 W2 W3 W4 W5 W6 W7');

  SELECT r, f.* 
  FROM #mywords
  CROSS APPLY OPENJSON(CONCAT('[["',REPLACE(w,' ','","'),'"]]')) 
  WITH
  (fifth VARCHAR(255) '$[4]') as f

+---+-------+
| r | fifth |
+---+-------+
| 1 | one   |
| 2 | W1    |
+---+-------+