Parsing a text string to locate variable length numbers

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
CH
Fan

Parsing a text string to locate variable length numbers

HER AB LCA1.30CO HER1.30NUC2.60END ROE0.844659 ATH AB PFO31.96NUC31.96END ROE0.844659 LCA AB ATH24.86CO LCA24.86NUC49.72END ROE0.844659 LON AB LCA180.04NUC180.04END ROE0.744255 Can someone help me figure out how to extract the 2 numbers from the text data above and put each on its own column? There are 4 examples shown. Field 1 - I need the number(with decimal) from above examples. Field 1 always follow the code NUC, it is represented as a dollar.cents, varied number of digits to the left of decimal. Field 2 – I need the number(with the decimal) from above examples It always follows code ROE, It is always at the end of the text field, It is always in the format of #.###### I have tried several versions of SUBSTR and REGEX, seem to have trouble finding the fields because they vary as to the length and the distance from the start of the field.

Accepted Solutions
Junior Contributor

Re: Parsing a text string to locate variable length numbers

You can use this RegEx to search for a at least one digit followed by a period followed by at least one digit after 'NUC', similar for 'ROE':

RegExp_Substr(col, '(?<=NUC)[0-9]+.[0-9]+')

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Parsing a text string to locate variable length numbers

You can use this RegEx to search for a at least one digit followed by a period followed by at least one digit after 'NUC', similar for 'ROE':

RegExp_Substr(col, '(?<=NUC)[0-9]+.[0-9]+')

 

CH
Fan

Re: Parsing a text string to locate variable length numbers

Thank you! This did the trick!