Oracle: get part string from a string

Question

I have a string from oracle table like this:

 SELECT col1 FROM MyTable;
 col1
 part1;part2;part3
 ....

How can I get a substring from col1 such as part2?

 SELECT col1, INSTR(col1, ';', 1, ...) SubStrPart2 // string func?
 col1       SubStrPart2
 ....       part2

Not sure what string functions I can use here so that I can easily update it to get a specific sub-string part out?

Answer 1

This is a good reference about those functions.

SELECT col1, SUBSTR(col1, INSTR(col1, ';', 1, ...)) SubStrPart2 // string func?
 col1       SubStrPart2
 ....       part2

You could also work with a CASE statement:

SELECT col1,
       CASE WHEN INSTR(col1, ';', 1, ...) > 0
            THEN SUBSTR(col1, INSTR(col1, ';', 1, ...))
            ELSE 'Other value'
       END AS SubStrPart2
...

See also these examples.

Answer 2

If you want the "slice" of col1 between the first and second occurrence of ';', excluded, then the right approach shoule be something like...:

select substr(col1, 
              instr(col1, ';', 1, 1)+1,
              instr(col1, ';', 1, 2)-instr(col1, ';', 1, 1)-1)

it's a bit complicated because you need to specify the first character to take and then the number of characters to take (and there might be an off-by-one here, needs checking, but I do believe those +1 and -1 are correct).

Answer 3

If you want to split out on semi-colons, you could try something like this:

select col1
     , regexp_substr(col1, '^[^;]') as part1
     , replace(regexp_substr(col1, ';[^;]+;'), ';') as part2
     , replace(regext_substr(col1, ';[^;]+$'), ';') as part3