All Forums General
eche 3 posts Joined 05/14
13 May 2014
How do I split a column between quantity and unit of measure

Hi,  I need your help about an issue I have with a column that comes from the source like this:

Size

5

6

1 LB

1.1 LB

10 LB

10LB

10LBS

15 LB

16 OZ

17 LB

2 LB

2 OZ

2.5 KG

2.5 LB

2.5#

2.5LB

20 CT

20 LB

200 CT

I need to split this column in two: one with the quantity and one with the unit of measure, teradta version is 13.1.  My last hope was otranslate but is not supported.  Other thing I tried is char2hexint and look for the numbers, but there are special characters as well.  I have searched many teradata forums and have not found a clue.  This has to be developed in data stage ETL.
I would greatly appreciate any help that you can give me in this.

Raja_KT 1246 posts Joined 07/09
13 May 2014

I think you need to write a udf.
In 13.1,  (regex) regular expressions may not be there. Just double check by running one small regex function.
I hate writing  substr(col, 1,1) , obtaining 1 character after another 
or LIKE operator iterativelyand compare with list of strings  :) .

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
15 May 2014

If you can install a UDF (talk to your DBA) the eCStrSpn from Ebay will be helpfull:
eCStrSpn(size,'0123456789.') will return the position of the last digit, based on that you can easily split it.
http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs

Dieter

You must sign in to leave a comment.