CODEWITHSUNDEEP
sqloracle10g
fras
fras

Reputation: 123

Parsing second word in string, in Oracle

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

Upvotes: 0

Views: 3868

Answers (2)

ankush
ankush

Reputation: 29

This can be little more simple if you know about regular expressions:

SELECT CASE WHEN REGEXP_COUNT('STRING', ' ') > 0 THEN
    REGEXP_SUBSTR ('STRING', '(\s)(\S*)',1,1)
ELSE
    'STRING'
END

FROM DUAL

Upvotes: -1

Roland Bouman
Roland Bouman

Reputation: 31991

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

Upvotes: 3

Related Questions