CODEWITHSUNDEEP
sqlsql-server
Mohd Maaz
Mohd Maaz

Reputation: 278

How to select nth and (n+1)th word from string SQL Server

I want to select third and fourth word from a string in SQL Server.

This is what I tried:

DECLARE @Var VARCHAR(100) = 'get 3rd and 4th word from this';

SELECT Word
FROM
    (
     SELECT Value AS Word,
            ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) RN
     FROM STRING_SPLIT(@Var, ' ')
    ) T
WHERE RN >= 3 AND RN <=4;

But the issue is STRING_SPLIT is not supported in my database.

Is there any workaround for it?

Upvotes: 0

Views: 504

Answers (1)

Thom A
Thom A

Reputation: 95564

Use a string splitter that supports ordinal position (STRING_SPLIT does not), such as DelimitedSplit8k_LEAD and this is trvial:

SELECT item
FROM dbo.DelimitedSplit8k_LEAD('get 3rd and 4th word from this',' ') DS
WHERE DS.itemnumber IN (3,4);

Upvotes: 1

Related Questions