using instr and substr functions to return part of a string 1

[bookouri]

I can't seem to get the right combination of string functions to get this done.
If anybody could point me in the right direction I would be grateful.

given a column containing values like:

abcd-x-100-a;:
abdc-y-101-a;:
abcd-z-102-c;:
abcd-zbc;:
abcd-x-101-a;:
abcd-x-102-a;:
abcd-y-201-c;:
abcd-opelha;:
abcd-prrp;:
abcd-g-222-e;:
bbcda-abc;:
bbcda-abd;:
bbdda-bbb;:

How can I select the substring of the value that lies between the first two '-', or between the first '-' and the end of the value, IF there is only one '-'?
The location of the '-' is not consistent, some values have two '-' some have three '-' and some values have only one '-'.

The result i need from the list above would be:
x
y
z
zbc
x
x
y
opelha
prrp
g
abc
abd
bbb

The fact that there is no consistency in number and location of the '-' is throwing me off.

 

[SantaMufasa]

between the first '-' and the end of the value, IF there is only one '-'

If this part of your specification is accurate, then the strings you want include the ";:" on the strings with a single hyphen, right?

If you want what you asked for, then some code that would work for you is:

select substr(str_val
             ,instr(str_val,'-')+1                               -- find the 1st hyphen
             ,decode(instr(str_val,'-',1,2)                      -- look for 2nd hyphen
                    ,0,length(str_val)                           -- if no 2nd, return rest
                    ,instr(str_val,'-',1,2)-instr(str_val,'-')-1 -- if 2nd, return 2nd string
                    )
             ) result
  from string;

RESULT
--------
x
y
z
zbc;:
x
x
y
opelha;:
prrp;:
g
abc;:
abd;:
bbb;:

If you want the ";:" stripped, and if every string has ";:" as a terminator, then you can use this code:

select substr(str_val
             ,instr(str_val,'-')+1                               -- find the 1st hyphen
             ,decode(instr(str_val,'-',1,2)                      -- look for 2nd hyphen
                    ,0,instr(str_val,';:') -instr(str_val,'-')-1 -- if no 2nd, return to ";:"
                    ,instr(str_val,'-',1,2)-instr(str_val,'-')-1 -- if 2nd, return 2nd string
                    )
             ) result
  from string;

RESULT
-------
x
y
z
zbc
x
x
y
opelha
prrp
g
abc
abd
bbb

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[bookouri]

PERFECT!

Thank you so much. I was going around and around with it and just could not get the right combination!