Search code examples
sqloracleoracle-sqldeveloper

Oracle SQL query: Split string by space and get the nth string

I have been searching this all over on stackoverflow and haven't able to find the answer yet.

Given String "hello my name is...", I would like to split the string by spaces and get the nth element of that string.

For example, after splitting: [hello, my, name, is...] the 1st element is "hello", 2nd element is "my", so on..

My attempt:

SELECT REGEXP_SUBSTR(help, ' ', 1, 2) from 
(select 'hello my name is...' as help from dual);

This is incorrect giving me an empty result. I think something wrong with the regex space.

Solution

  • You are pretty close. You just need to search for non-spaces, because that is what you want to return:

    select REGEXP_SUBSTR(help, '[^ ]+', 1, 2)
from (select 'hello my name is...' as help from dual);