How strip of the non numeric data from VARCHAR field

Analytics

How strip of the non numeric data from VARCHAR field

Hi,

 We have a situation like we need to strip of non numeric values and collect only numeric values from VARCHAR(30) field. 

Below is the sample data and VARCHAR field can have any of the below values

1) A12345

2)AB12345

3)AB-12345

4)ABCD12345

Currently I used the 4 case statements and getting the required numerics with help of substr and position functions. Now the problem with this is if we get another non-numeric in  5th/6th/7th positions then i need to add another couple of case statements. This will make ugly if I keep on adding case statements upto 30 as my column size is 30.

Please let me know if there is any other generalized code with out adding case statements.

Regards,

Raj. 

7 REPLIES
Junior Supporter

Re: How strip of the non numeric data from VARCHAR field

Raj:

Under certain circumstances you could try something like this:

http://carlosal.wordpress.com/2012/11/12/only-numbers-en-teradata-ii/

HTH.

Cheers.

Carlos.

Re: How strip of the non numeric data from VARCHAR field

Oracle UDFs

translate

Offers functionality not available in Teradata SQL. Install this as otranslate(), because we already have a TRANSLATE.

create set volatile table tbl1 (

col1 varchar(20)

, col2 int

) primary index (col1)

on commit preserve rows

;insert into tbl1 Values ( 'A123456', )

;insert into tbl1 Values ( 'AB15234', )

;insert into tbl1 Values ( 'AB-12354', )

;insert into tbl1 Values ( 'ABCD12453', )

;select * from tbl1

;Update tbl1

Set col2 =

trim(syslib.otranslate(col1, 'ABCDEFGHIJKLMNOPQRSTURVWXYZ `~!@#$''%^&*()-_+=[{]}\|";:/?.>,<¨¢¹¦Ä¬—éË‘ÿ’‚ß¤Ý', '') )(varchar(20))

where col2 is null

;select *

from tbl1

;

col1                 col2

1 ABCD12453 12,453

2 A123456      123,456

3 AB-12354      12,354

4 AB15234       15,234

Enthusiast

Re: How strip of the non numeric data from VARCHAR field

Hi Guys,

I have a requirement.

I have a field col1 Varchar(10) which has data like '389','CK321','CKRES'  etc .. 

I want to filter the value which is having only Integers. That means, i want to see only 'CK321' and 'CKRES' in my output.

we are using REGEXP_SIMILAR(col1,  '[[:alpha:][:blank:]]+'   ,    'i') =1, but it filters out 'CK321' also ..

Please suggest.

Enthusiast

Re: How strip of the non numeric data from VARCHAR field

WHERE  REGEXP_INSTR(TRIM ( COLUMN_NAME ), '^[-+]?([0-9]+(\.[0-9]*)?|\.[0-9]+)$')  = 0 

please try applying the above RegEx.

Enthusiast

Re: How strip of the non numeric data from VARCHAR field

thank for your response Kirti. 

That function is filtering out lot of my records which contains Ineteger and Char combination.

I just want to filter my column where it has ONLY integers nothing else  .. like 123, 455, 9999373 etc etc .. but not 'abc123' and 'xyz6664' ... 

Enthusiast

Re: How strip of the non numeric data from VARCHAR field

SEL 'TRUE' 
WHERE   REGEXP_INSTR(TRIM('32A1' ), '^[-+]?([0-9]+(\.[0-9]*)?|\.[0-9]+)$')  = 0

-- Returns true

SEL 'TRUE'
WHERE   REGEXP_INSTR(TRIM('Xyz321' ), '^[-+]?([0-9]+(\.[0-9]*)?|\.[0-9]+)$')  = 0

-- Returns true

SEL 'TRUE'
WHERE   REGEXP_INSTR(TRIM('321Xyz' ), '^[-+]?([0-9]+(\.[0-9]*)?|\.[0-9]+)$')  = 0

-- Returns True

SEL 'TRUE'
WHERE   REGEXP_INSTR(TRIM('321' ), '^[-+]?([0-9]+(\.[0-9]*)?|\.[0-9]+)$')  = 0

-- No rows returned, Filters only the numeric values
Junior Contributor

Re: How strip of the non numeric data from VARCHAR field

If you only want to digits only:

 WHERE REGEXP_SIMILAR(col1, '^[0-9]*$') = 0

Or

WHERE RTRIM(col1, '0123456789') = ''