Finding first and second word in a string in SQL Developer

Question

How can I find the first word and second word in a string separated by unknown number of spaces in SQL Developer? I need to run a query to get the expected result.

String:

Hello     Monkey this         is me

Different sentences have different number of spaces between the first and second word and I need a generic query to get the result.

Expected Result:

Hello
Monkey

I have managed to find the first word using substr and instr. However, I do not know how to find the second word due to the unknown number of spaces between the first and second word.

select substr((select ltrim(sentence) from table1),1,
(select (instr((select ltrim(sentence) from table1),' ',1,1)-1) 
from table1)) 
from table1

score 1 · Answer 1 · answered Aug 04 '14 at 04:49

Since you seem to want them as separate result rows, you could use a simple common table expression to duplicate the rows, once with the full row, then with the first word removed. Then all you have to do is get the first word from each;

WITH cte AS (
  SELECT value FROM table1
  UNION ALL  
  SELECT SUBSTR(TRIM(value), INSTR(TRIM(value), ' ')) FROM table1
)
SELECT SUBSTR(TRIM(value), 1, INSTR(TRIM(value), ' ') -1) word
FROM cte

Note that this very simple example assumes that there is a second word, if there isn't, NULL will be returned for both words.

An SQLfiddle to test with.

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

While Joachim Isaksson's answer is a robust and fast approach, you can also consider splitting the string and selecting from the resulting pieces set. This is just meant as hint for another approach, if your requirements alter (e.g. more than two string pieces).

You could split finally by the regex /[ ]+/, and so getting the words between the blanks.

Find more about splitting here: How do I split a string so I can access item x?

This will strongly depend on the SQL dialect you are using.

edited May 23 '17 at 12:29

Community

1
1

answered Aug 04 '14 at 05:04

peter_the_oak

3,529
3
23
37

It is interesting how this question has no trivial solution. – peter_the_oak Aug 04 '14 at 05:05

Jaugar Chang · Answer 3 · 2014-08-04T05:21:31.467

Try this with REGEXP_SUBSTR:

SELECT
  REGEXP_SUBSTR(sentence,'\w+\s+'),
  REGEXP_SUBSTR(sentence,'\s+(\w+)\s'),
  REGEXP_SUBSTR(sentence,'\s+(\w+)\s+(\w+)'),
  REGEXP_SUBSTR（REGEXP_SUBSTR(sentence,'\s+(\w+)\s+(\w+)'),'\w+$'),
  REGEXP_SUBSTR(sentence,'\s+(\w+)\s+$')
FROM table1;

result:

1       2       3               4       5
Hello   Monkey  Monkey this     this    is_me

Learn more about REGEXP_SUBSTR reference to Using Regular Expressions With Oracle Database

Test use SqlFiddle： http://sqlfiddle.com/#!4/8e9ef/9

If you only want to get the first and the second word, use REGEXP_INSTR to get second word start position :

SELECT
  REGEXP_SUBSTR(sentence,'\w+\s+') AS FIRST,
  REGEXP_SUBSTR(sentence,'\w+\s',REGEXP_INSTR(sentence,'\w+\s+')+length(REGEXP_SUBSTR(sentence,'\w+\s+'))) AS SECOND
FROM table1;

Finding first and second word in a string in SQL Developer

3 Answers3