Extract substring begining with the first numbered character

Database
New Member

Extract substring begining with the first numbered character

Hello,

Newbie here.  I am looking to extract all characters begining with the first digit/number character.  Is this possible without having to wright another query?  I am currently using the syntax below, asking to return 7 characters, begining with the 5th character after the letter 'R' from the below text. This gives me the desired result of 5751965.  However, the 7 digits I am looking for do not always fall on the 5th character after the 'R'.  Can anyone help to pull all characters begining with the first digit/number?  I have looked but have not been able, any help is appreciated.

 

ACCT IATA NBR     SAO 5751965

substr(trim(SUBSTRING(REMRK_TXT FROM POSITION('R' IN REMRK_TXT)+1)), 5,7) as Number

2 REPLIES
Teradata Employee

Re: Extract substring begining with the first numbered character

regexp_substr(REMRK_TXT,'[0-9]{7}')

Teradata Employee

Re: Extract substring begining with the first numbered character

Use REGEXP_SUBSTR. To extract the first digit 0-9 and all characters to the right:

REGEXP_SUBSTR(REMRK_TXT,'[0-9].*')