Finding the Sub String Between < and >

General

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

4 REPLIES
Senior Apprentice

Re: Finding the Sub String Between < and >

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

Re: Finding the Sub String Between < and >

Thx a lot Dieter ..that helps :)

Re: Finding the Sub String Between < and >

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

Teradata Employee

Re: Finding the Sub String Between < and >

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, '(?<=\().*?(?=\))')