select value from string with varying position

Database
Enthusiast

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?

Thanks,

MIKE

3 REPLIES
Senior Apprentice

Re: select value from string with varying position

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]+')
Enthusiast

Re: select value from string with varying position

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.

Mike

Enthusiast

Re: select value from string with varying position

Works great.  Thanks for your help with this.  

Very much appreciated!

mike