How can I retrieve first second and third word of a String in SQL?

Question

I need a query which would extract the first second and third word of a string.

I have approximately 5 words in each row and I need only the first three words out of 5 in the same row (1 row). Example "ATV BDSG 232 continue with other words".

I need only the first three words together in one row (in the same row) like "ATV BDSG 232" as a first row. The table has about 1000 rows and at the end of it I should have 1000 rows again but each row should contain only the first three words of the string.

I found a query which works fine for extracting first two like "ATV BDSG" discussed in stack overflow. The query is

"SELECT SUBSTRING(field1, 0, CHARINDEX(' ', field1, CHARINDEX(' ', field1, 0)+1)) FROM Table"

Can we derive this for extracting first three words?

Thanks in advance

why question voted down 4 times? How about giving the questioner a reason? — Cato, Jan 03 '19 at 17:25

Ilyes · Answer 1 · 2019-01-03T17:14:14.190

Things are easy, SQL Server provide STRING_SPLIT() function make that too easy

DECLARE @Var VARCHAR(100) = 'ATV BDSG 232 Continue with other words';

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

But since you are working on 2012 version, you need to define your own function.

You can also take the hard way, first you need to get the first word, then replace it with '' and get the second word, then do the same for the 3rd word as

DECLARE @Var VARCHAR(100) = 'ATV BDSG 232 Continue with other words';

WITH FW AS
(
  SELECT LEFT(@Var, CHARINDEX(' ', @Var)) FirstWord
),
 SW AS
(
  SELECT LEFT(REPLACE(@Var, FirstWord, ''),
              CHARINDEX(' ', REPLACE(@Var, FirstWord, ''))) SecondWord
  FROM FW
)
SELECT FirstWord,
       SecondWord,
       LEFT(REPLACE(REPLACE(V, FirstWord, ''), SecondWord, ''),
       CHARINDEX(' ', REPLACE(REPLACE(V, FirstWord, ''), SecondWord, ''))
       ) ThirdWord
FROM
    (
     SELECT *, @Var V
     FROM FW CROSS APPLY SW
    ) T

Demo

UPDATE

If you want to select the three first words then simply

SELECT SUBSTRING(Str, 0, CHARINDEX(' ', Str, CHARINDEX(' ', Str, CHARINDEX(' ', Str, 0)+1)+1)) Words
FROM Strings

Demo

Zack · Accepted Answer · 2019-01-03T16:29:38.910

2

If you don't want to create a dedicated function, you can use successive CROSS APPLYs:

SELECT
    T.s,
    FirstSpace.i,
    SecondSpace.j,
    ThirdSpace.k,
    CASE
        When ThirdSpace.k > 0 THEN LEFT(T.s, Thirdspace.k - 1)
        ELSE T.S
    END AS Phrase
FROM t
    CROSS APPLY (SELECT CHARINDEX(' ', T.s, 1)) AS FirstSpace(i)
    CROSS APPLY (SELECT CHARINDEX(' ', T.S, FirstSpace.i + 1)) AS SecondSpace(j)
    CROSS APPLY (SELECT CHARINDEX(' ', T.s, SecondSpace.j + 1)) AS ThirdSpace(k)

gives you the results you need:

|                   s                    | i | j | k  | phrase           |
|----------------------------------------|---|---|----|------------------|
| ATV BDSG 232 Continue with other words | 4 | 9 | 13 | ATV BDSG 232     |

edited Jan 03 '19 at 16:29

answered Jan 03 '19 at 16:20

Zack

2,220
1
8
12

I need only S table. I got this error "Invalid length parameter passed to the LEFT or SUBSTRING function." @zack – learningsql Jan 03 '19 at 16:25
@learningsql: Do you have records that don't contain more than 3 words? That would produce the error. I'll update my answer to resolve that issue. – Zack Jan 03 '19 at 16:29
All are greater than 3 words @zack – learningsql Jan 03 '19 at 16:30

score 0 · Answer 3 · answered Jan 03 '19 at 17:23

 --make some test data

declare @test as nvarchar(100) = 'my test   string  for words';

select 1 id, cast('my test   string  for words' as nvarchar(max)) word into #test;
insert #test (id,word) values (2,'a  b c d e f g hhh   yyyyyy') ;
insert #test (id,word) values (3,'  a required test string  d e f g hhh   yyyyyy') ;
insert #test (id,word) values (4,'a quick test') ;
insert #test (id,word) values (5,'a test') ;
insert #test (id,word) values (6,'last') ;

--break up letters, count the first 3 words
;WITH CTE AS (SELECT 1 x, substring(@test,1,1) charx
             UNION ALL
             SELECT X + 1, substring(@test,x + 1,1) from CTE WHERE x < len(@test) 
             )
             select * from cte c3 where (SELECT count(0) cnt FROM CTE c1 JOIN CTE c2 on c1.x <= c3.x and c1.x + 1 = c2.x and c1.charx =' ' and c2.charx != ' ') < 3



;WITH   tabx as (select id, cast(ltrim(word) as nvarchar(max)) 'word' from #test),    --do some ltrim
        CTE AS (
                SELECT id, 1 x, substring(word,1,1) charx from tabx
                UNION ALL
                SELECT t.id, c.X + 1, substring(t.word,x + 1,1)  
                        from tabx t 
                            JOIN CTE c on c.id =  t.id and x < len(t.word) 
             ),
        disj as
             (select * from cte c3 where 
                        (SELECT count(0) cnt 
                                FROM CTE c1 
                                JOIN CTE c2 on c1.id = c3.id and c1.id = c2.id and c1.x <= c3.x and c1.x + 1 = c2.x and c1.charx =' ' and c2.charx != ' '
                        ) < 3
                ),
        rj as 
            (select disj.id,disj.x, disj.charx z  
                                from disj 
                                where disj.x = 1 
                UNION ALL 
                    select d.id, d.x, r.z + d.charx    
                            FROM rj r 
                            join disj d on r.id = d.id and r.x + 1 = d.x
            )
                select *  
                    from rj r1  
                        cross apply (select max(r2.x) TheRow from rj r2 where r1.id = r2.id) dq
                    where r1.x = dq.TheRow  
                    order by r1.id;

 --delete test data
drop table #test

score 0 · Answer 4 · answered Feb 11 '20 at 18:49

/* This is not perfect - but interesting */
declare @t table (fullname varchar(100))
insert @t values('Mr Jones'),('Mrs Amy smith'),('Jim Smith'),('Dr Harry Web '),('Paul Fred andrew jones')

select fullname,

a.value as a ,
b.Value as b,
c.Value as c,
d.Value as d,
e.Value as e,
f.value as f

from @t
outer apply (select top 1 value from STRING_SPLIT(fullname, ' '))  a 
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value ))  b
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value,b.value ) ) c
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value,b.value,c.value ))  d
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value,b.value,c.value,d.value) ) e
outer apply (select top 1 value from STRING_SPLIT(fullname, ' ')   where value not in (a.value,b.value ,c.value,d.value,e.value) ) f

score 0 · Answer 5 · answered Dec 14 '20 at 06:21

0

To Select First Word -

Select top 1 Ltrim(Rtrim(value)) FROM STRING_SPLIT(@input,' ')

To Select Only Second Word -

Select Ltrim(Rtrim(value)) from STRING_SPLIT(@input,' ') Order by (Select NULL) OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

answered Dec 14 '20 at 06:21

Taha Ali

457
1
5
7

How can I retrieve first second and third word of a String in SQL?

5 Answers5

Linked