All Forums General
paranoid 2 posts Joined 04/16
01 Apr 2016
Finding the Sub String Between < and >

Hi All,
 
I am new to Teradata SQL and still in learning stage. I am sure that i will improve my skills in some time.
 
Could you please help me in finding out the substring between two characters < and >?
 
Thanks in avance,
 
Regards,
Sue

dnoeth 4628 posts Joined 11/04
02 Apr 2016

Hi Sue,
you can do that using combinations of POSITION/INSTR and SUBSTRING, but the easiest way is a regular expression:

REGEXP_SUBSTR(x, '(?<=<).*?(?=>)')

(?<=<) Lookbehind searching for '<' without adding it to the result
.*? Any number of characters, stopping before the first '>'
(?=>) Lookahead searching for '>' without adding it to the result
 
Regular expressions are a kind of programming language :-)
There's not much documentation within the Teradata manuals but you'll find oodles of web sites, you just have to check if it's the regex idiom Teradata uses...

Dieter

paranoid 2 posts Joined 04/16
03 Apr 2016

Thx a lot Dieter ..that helps :)

srikanthmatta44 1 post Joined 07/16
26 Jul 2016

Hello-
What if I want between '(' and ')'?  I tried to use the same logic but did not work.  I am getting paranthesis in the result set.
I appreciate your help.
Thanks
Srikanth

Fred 1096 posts Joined 08/04
30 Jul 2016

In (?<=<) the first < is special (combined with ? and =  makes a lookbehind rather than lookahead) but the second is treated as a literal '<' character based on context. But for a literal parenthesis in this same context, you will need to escape with \
REGEXP_SUBSTR(x, '(?<=\().*?(?=\))')

You must sign in to leave a comment.