All Forums Database
Nikhil_Teradata 24 posts Joined 10/13
22 Jul 2015
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

 

--Nik

"Learn, Learn, Learn and your life would never be boring"

Glass 225 posts Joined 04/10
23 Jul 2015

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

Nikhil_Teradata 24 posts Joined 10/13
23 Jul 2015

Works perfect. Thank you very much Mr. Glass. 

--Nik

"Learn, Learn, Learn and your life would never be boring"

dnoeth 4628 posts Joined 11/04
23 Jul 2015

Hi Nik,
in TD14 you can also use a Regular Expression:
REGEXP_SUBSTR(col, '[0-9]+')

Dieter

Nikhil_Teradata 24 posts Joined 10/13
23 Jul 2015

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. 

--Nik

"Learn, Learn, Learn and your life would never be boring"

You must sign in to leave a comment.