CODEWITHSUNDEEP
sqlsql-server
Ancita
Ancita

Reputation: 85

How to get the 3rd word from a text in SQL Server

Can someone please help on the query for getting the 3rd word from a text in SQL Server? The below query retrieves the 2nd word, but I need the 3rd word. Thanks.

SELECT substring(@sentence,charindex(' ',@sentence), CHARINDEX(' ',ltrim(SUBSTRING(@sentence,charindex(' ',@sentence),LEN(@sentence)-charindex(' ',@sentence)))) )

Upvotes: 1

Views: 599

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82000

Using a bit of JSON you can get the 3rd word

Select * 
      ,NewVal = JSON_VALUE('["'+replace(string_escape(SomeCol,'json'),' ','","')+'"]' ,'$[2]')
 From YourTable

Or for something a little more robust, use a CROSS APPLY

Select A.*
      ,Pos1 = JSON_VALUE(JS,'$[0]')
      ,Pos2 = JSON_VALUE(JS,'$[1]')
      ,Pos3 = JSON_VALUE(JS,'$[2]')
      ,Pos4 = JSON_VALUE(JS,'$[3]')
 From YourTable A
 Cross Apply (values ( '["'+replace(string_escape(SomeCol,'json'),' ','","')+'"]' ) ) B(JS)

If using a variable

 Declare @sentence varchar(max) = 'This is only a test'
 Select JSON_VALUE('["'+replace(string_escape(@sentence,'json'),' ','","')+'"]' ,'$[2]')

Upvotes: 1

Related Questions