user565992 user565992 - 1 month ago 7
SQL Question

split a string on multiple delimiters

I have a sql that returns the example string below:

Input

PKIND:BCMOX:10048301-


output
BCMOX:10048301


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

vkp vkp
Answer

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