How do I split a column between quantity and unit of measure

General
Fan

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.

2 REPLIES
Enthusiast

Re: How do I split a column between quantity and unit of measure

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  :) .

Senior Apprentice

Re: How do I split a column between quantity and unit of measure

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