All Forums Analytics
prudhviy2k 1 post Joined 08/09
31 Oct 2012
How strip of the non numeric data from VARCHAR field

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

CarlosAL 512 posts Joined 04/08
12 Nov 2012

Under certain circumstances you could try something like this:

paul43438 2 posts Joined 09/04
14 Nov 2012

Oracle UDFs
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

ASCHARAN 10 posts Joined 09/15
23 Oct 2015

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.

kirthi 65 posts Joined 02/12
23 Oct 2015
WHERE  REGEXP_INSTR(TRIM ( COLUMN_NAME ), '^[-+]?([0-9]+(\.[0-9]*)?|\.[0-9]+)$')  = 0 

please try applying the above RegEx.

ASCHARAN 10 posts Joined 09/15
23 Oct 2015

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' ... 

kirthi 65 posts Joined 02/12
23 Oct 2015

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

-- Returns true 

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

-- Returns true 

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

-- Returns True

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

-- No rows returned, Filters only the numeric values


dnoeth 4628 posts Joined 11/04
23 Oct 2015

If you only want to digits only:

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


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



You must sign in to leave a comment.