First two words in the string - sql server

Question

I have string like this " This is a hello world example"

Now I want first two words of the sentence as my output in SQL Server. i.e. This is .

Another example: Original sentence : "Complete word exercise" Output: Complete word

Answer 1

You can use a query as follows:

DECLARE @d nvarchar(100)
SET @d = 'Complete word exercise'
SELECT SUBSTRING(@d, 0, CHARINDEX(' ', @d, CHARINDEX(' ', @d, 0)+1))

Or alternatively when used in a query:

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

Answer 2

Many solutions will break for any strings that have less than 2 words, which is increasingly likely for people hoping to parse the first n number of words.

Let's first look at the query, and then how we can tell if it actually evaluated correctly.
For that, we need to nest multiple CHARINDEX statements, which take the following params:

CHARINDEX( expressionToFind , expressionToSearch [ , start_location ] )

Charindex will return the first index where it finds the specific string. What we keep doing is kicking the ball down the road by adding in a start_location equal to the first found instance +1 so it'll find the 2nd, 3rd, 4th instance, etc. Also, instead of SUBSTRING(@str, 0,... we can just use LEFT(@str,... to capture the first portion of the string, but calculating how far deep to go is the hard part anyway:

DECLARE @string VARCHAR(1000) = 'One Two Three';
SELECT LEFT(@string, CHARINDEX(' ', @string,
                     CHARINDEX(' ', @string,
                     CHARINDEX(' ', @string,
                     CHARINDEX(' ', @string)+1)+1)+1))

But this will fail if we don't have the minimum number of words:
^{Technically, it'll just keep looping around infinitely. Once it runs out of spaces, it'll start indexing again from the beginning}

Pop quiz... what will the above query resolve to? _{Hover below for answer}

SELECT LEFT('One Two Three', 3) -- 'One'

In the solution in Extracting a specific number of words from a string in sql, we can check that the last CHARINDEX produced a non-zero value, meaning it hit a space at that level of depth. But also, since the nested charindex is kind of unwieldy, we can get at that information at little more directly by counting the number of occurrences of a certain substring in a SQL varchar?

So the final solution should look like this to grab the first 4 words:

DECLARE @string VARCHAR(1000) = 'One Two Three Four Five Six';

SELECT CASE WHEN LEN(@string)-LEN(REPLACE(@string, ' ', '')) < 4 
            THEN @string
            ELSE LEFT(@string, CHARINDEX(' ', @string,
                               CHARINDEX(' ', @string,
                               CHARINDEX(' ', @string,
                               CHARINDEX(' ', @string)+1)+1)+1))
       END

If there are less than 4 spaces, we'll just return the whole string. For more than four, we'll find the position of the 4th space and return the left portion of the string all the way to that position

Answer 3

ORACLE ONLY - You can also use INSTR like this:

SELECT SUBSTR(colName, 0, INSTR(colName,' ',1,2)) FROM table

Note: If row data has less than 2 words, empty row will be returned