All Forums Database
hazard01 5 posts Joined 11/14
24 Nov 2014
select value from string with varying position

Hi  Hope you can help. 
I am trying to return a monetary value from a string where the position of the value varies in each row.
Typical examples:
BA/DC:   12/23:111C  DATE:01/01/1900 AMT:     $5.99
AB/CD: 45/67:456A DATE:01/01/1900 AMT: $5.99

In short is it possible to return all characters from and including $ until the next space or end of row in a new field so it just contains the monetary value?

dnoeth 4628 posts Joined 11/04
26 Nov 2014

Hi Mike,
what's your Teradata release?
In TD14 you can use REGEXP_SUBTR to extract a price:

REGEXP_SUBSTR(col, '\$[0-9]*.[0-9]+')



hazard01 5 posts Joined 11/14
26 Nov 2014

Hi there,
I am using version 14 so I will give it a try shortly and let you know.  That's a new one on me so thanks for the heads up.

hazard01 5 posts Joined 11/14
01 Dec 2014

Works great.  Thanks for your help with this.  
Very much appreciated!

You must sign in to leave a comment.