Bdtjtk

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

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     |

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

 --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