Parsing second word in string, in Oracle.

Question

I need to write a bit of SQL which parses the second word of any given string, or if there is no space, the entire string.

The logic is

'XXX YYYYY ZZZZZ' would return YYYY,

'XXX YYY' would return YYY and

'XXXXXX' would return XXXXXX.

Any suggestions? Is there a way of using SUBSTR and the 2 positions within the string? Rather an using the length.

Thanks very much

Answer 1

case
    -- at least two words 
    when instr(c, ' ', 1, 1) > 0 then
        case
            -- at least three words 
            when instr(c, ' ', 1, 2) > 0 then
                -- return text between first and last space
                substr(
                    c
                ,   instr(c, ' ', 1, 1)+1                          
                ,   instr(c, ' ', 1, 2) - instr(c, ' ', 1, 1) - 1 
                )
            else
            -- return 2nd word (text after first space)
                substr(c, instr(c, ' ', 1, 1)+1)
        end
    -- one word, return it
    else c
end