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

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

Answer

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     |