CODEWITHSUNDEEP
sqldatabasestringoracle-sqldeveloper
Jason
Jason

Reputation: 1

Finding first and second word in a string in SQL Developer

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

Upvotes: 0

Views: 8043

Answers (3)

Jaugar Chang
Jaugar Chang

Reputation: 3196

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;

Upvotes: 0

peter_the_oak
peter_the_oak

Reputation: 3710

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.

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

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.

Upvotes: 1

Related Questions