Variable length substring with multiple delimiters

Database
Enthusiast

Variable length substring with multiple delimiters

Hi,

I have below variables in my result set. These are result of an substring function.

734746/Chi
1435919?c=
762799?c=E
710632?c=E
20784/T23

I want to get only the numeric values before first delimiter(/, ?). So the result set should look like this.

734746
1435919
762799
710632
20784

Can somebody please help me write a substring for this? The issues I have are 

1. Length of result set varies between 4 to 7

2. First delimiter can be either ? or /

Thanks in advance.

-Nik

4 REPLIES
Enthusiast

Re: Variable length substring with multiple delimiters

Nik,

this will work using substring.


sel case when col like '%/%' then substr(col,1,position ('/' in col) -1 )
else substr(col,1,position ('?' in col) -1 )
end
from

Rglass

Enthusiast

Re: Variable length substring with multiple delimiters

Works perfect. Thank you very much Mr. Glass. 

Senior Apprentice

Re: Variable length substring with multiple delimiters

Hi Nik,

in TD14 you can also use a Regular Expression:

REGEXP_SUBSTR(col, '[0-9]+')

Enthusiast

Re: Variable length substring with multiple delimiters

I was wondering if there was something like this in place for TD. I learnt about regexp_substr through the other thread "help needed in a regular expression".

Thanks a lot. I will work with both of these.