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,

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...


paranoid 2 posts Joined 04/16
03 Apr 2016

Thx a lot Dieter ..that helps :)

srikanthmatta44 1 post Joined 07/16
26 Jul 2016

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.

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.