Return word in a string

Question

How can I return the second word in a varchar2 data type and blank if there is nothing? The idea is to use a function within a function.

A regular expression is probably what you need. Use it in the regexp_substr function. — Colin 't Hart, Mar 18 '13 at 12:44

score 4 · Answer 1 · answered Mar 18 '13 at 13:01

4

regexp_substr(column, '\S+', 1, 2)

answered Mar 18 '13 at 13:01

Egor Skriptunoff

paul · Accepted Answer · 2013-03-18T13:12:53.303

2

substr(column, instr(column, ' ') + 1)

edit (for second word only):

substr(col, instr(col, ' '), instr(col, ' ', instr(col, ' ') + 1) - instr(col, ' '))

edit again:

as pointed out by Colin, REGEXP_SUBSTR(col,'\S+',1,2) might be a better way of doing this

edited Mar 18 '13 at 13:12

answered Mar 18 '13 at 12:45

paul

was just about to post this... here's the sqlfiddle of it: http://sqlfiddle.com/#!2/07093/2 – rbedger Mar 18 '13 at 12:46
1

And how does this return just the second word and not also subsequent words? – Colin 't Hart Mar 18 '13 at 12:46
It doesn't. I just tested it – Dave Mar 18 '13 at 12:50
So `select substr('one two three', instr('one two three', ' ') + 1) from dual` returns just "two"? I would expect "two three". – Colin 't Hart Mar 18 '13 at 12:51
Beware the difference between `\w` and `\S`. In this case you probably want the latter. – Colin 't Hart Mar 18 '13 at 13:06

2 Answers2