Help With REGEXP_SUBSTR Teradata

General
New Member

Help With REGEXP_SUBSTR Teradata

SyntaxEditor Code Snippet

 

I'm looking for some help with the REGEXP_SUBSTR function

 

e.g string: HGTRDEF^004675634^fistname^lastname^^fdggfggd&

 

I I'm looking for:

1. The numbers from between '^00' and ^ e.g 004675634

2. The first name which always come after the numbers between '^' and '^' e.g firstname

3. The last name which is always before the '^^' e.g lastname

 

My idea was to search between the ^00 and the ^^ and then break it apart that way. e.g HGTRDEF^004675634^fistname^lastname^^fdggfggd&

 

SELECT
REGEXP_SUBSTR
('HGTRDEF^004675634^fistname^lastname^^fdggfggd&','(?<=^00).(?>=^)') AS EG

 

Any help would good :-)

 

SyntaxEditor Code Snippet

 

 

 

James

1 REPLY
Senior Apprentice

Re: Help With REGEXP_SUBSTR Teradata

Hi James,

 

The following is probably not the prettiest way to do this but it seems to work on some sample data that I created:

SELECT t1.*
  ,REGEXP_INSTR(col2,'\^\^00[0-9]*\^',1,1,0,'i') AS nbr_start
  ,REGEXP_INSTR(col2,'\^',nbr_start+4,1,0,'i') AS nbr_end
  ,REGEXP_INSTR(col2,'\^',nbr_end+1,1,0,'i') AS text1_end
  ,REGEXP_INSTR(col2,'\^\^',text1_end+1,1,0,'i') AS text2_end
  ,SUBSTRING(col2 FROM (nbr_start+2) FOR (nbr_end - nbr_start - 2)) AS nbr2
  ,SUBSTRING(col2 FROM (nbr_end+1) FOR (text1_end - nbr_end - 1)) AS text1
  ,SUBSTRING(col2 FROM (text1_end+1) FOR (text2_end - text1_end - 1)) AS text2

FROM t1
ORDER BY 1

As you can probably work out, I've used REGEXP_INSTR just to find the start/end points of each component that you're looking for and then a simple SUBSTRING to extract the actual values (as I said, not pretty).

I tried using the REGEXP_SUBSTR but that included the delimiters - for instance the 'number' was returned as '^^0012345^'. There is probably a way to sort that but nothing came to me quickly.

 

Does that help?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com