user565992 user565992 - 11 months ago 54
SQL Question

split a string on multiple delimiters

I have a sql that returns the example string below:




I need to write code the first substring the string on
then split it on
and return the 2 & 3 item (

vkp vkp
Answer Source

If the string format is consistent and you want to extract everything after the first : until the first occurrence of -, use a combination of substr and instr.

select substr(col, instr(col,':')+1, instr(col,'-')-instr(col,':')-1)
from yourtable
where instr(col,':') > 0 and instr(col,'-') > 0 --to get the rows which have these 2 characters