# Miscellaneous > SQL Scripts >  Strip field of Num numeric characters

## sleezy

How do l clean a field that has funny characters. l want to remove anything that is not numeric and if the lenght of the numeric characters is less than 13 pad it with zeros? 

i.e 234rfg!*23 and just remain with 23423 then pad it with zeros to make it 13 characters  0000000023423.

So the first thing l would like to do is to check if its numeric if so check the length and make sure its 13 long. How do l do that

----------


## JBane

Sleezy,
Long ugly code, but at least you only have to run it once. I've used a test table to demonstrate:

create table numtest (col1 char(15))

insert into numtest
values
('1#$234(*)5!!saf')

insert into numtest
values
('1234567890123')

Now that we have some data, the first thing to do is get rid of the non-numeric characters. This example assumes the column is up to 15 characters long, you'll need to cut and paste if you have more:

UPDATE NUMTEST SET COL1 =
CASE
WHEN substring(col1, 1,1) LIKE '[0-9]'
THEN substring(col1, 1,1) ELSE '' END +
CASE
WHEN substring(col1, 2,1) LIKE '[0-9]'
THEN substring(col1, 2,1) ELSE '' END +
CASE
WHEN substring(col1, 3,1) LIKE '[0-9]'
THEN substring(col1, 3,1) ELSE '' END +
CASE
WHEN substring(col1, 4,1) LIKE '[0-9]'
THEN substring(col1, 4,1) ELSE '' END +
CASE
WHEN substring(col1, 5,1) LIKE '[0-9]'
THEN substring(col1, 5,1) ELSE '' END +
CASE
WHEN substring(col1, 6,1) LIKE '[0-9]'
THEN substring(col1, 6,1) ELSE '' END +
CASE
WHEN substring(col1, 7,1) LIKE '[0-9]'
THEN substring(col1, 7,1) ELSE '' END +
CASE
WHEN substring(col1, 8,1) LIKE '[0-9]'
THEN substring(col1, 8,1) ELSE '' END +
CASE
WHEN substring(col1, 9,1) LIKE '[0-9]'
THEN substring(col1, 9,1) ELSE '' END +
CASE
WHEN substring(col1, 10,1) LIKE '[0-9]'
THEN substring(col1, 10,1) ELSE '' END +
CASE
WHEN substring(col1, 11,1) LIKE '[0-9]'
THEN substring(col1, 11,1) ELSE '' END +
CASE
WHEN substring(col1, 12,1) LIKE '[0-9]'
THEN substring(col1, 12,1) ELSE '' END +
CASE
WHEN substring(col1, 13,1) LIKE '[0-9]'
THEN substring(col1, 13,1) ELSE '' END +
CASE
WHEN substring(col1, 14,1) LIKE '[0-9]'
THEN substring(col1, 14,1) ELSE '' END +
CASE
WHEN substring(col1, 15,1) LIKE '[0-9]'
THEN substring(col1, 15,1) ELSE '' END 


Now that we have only numeric values, we can get the length of each number and pad the zeros out to 13 for any column that is less than that length:

update numtest
set col1 =  rtrim(ltrim(col1)) + replicate( '0', 13- len(col1)) 
where len(rtrim(ltrim(col1))) < 13

Giving the result:


select * from numtest


col1            
--------------- 
1234500000000  
1234567890123  

(2 row(s) affected)




As always, back up your table first.


Jeff

----------


## andi_g69

Using a similar algorithm, however the code a bit condensed. I have used the same sample table...

CREATE FUNCTION dbo.udf_stripNum (@sVal varchar (15)) RETURNS char (15) AS
BEGIN
DECLARE @iCount int, @sNumVal varchar (30)
SELECT @iCount = 1, @sNumVal = '000000000000000'
While @iCount <= Len (@sVal) BEGIN
	IF SUBSTRING (@sVal, @iCount, 1) LIKE '[0-9]' SET @sNumVal = @sNumVal + SUBSTRING (@sVal, @iCount, 1)
	SET @iCount = @iCount + 1
END
SET @sNumVal = RIGHT (@sNumVal, 15)
RETURN @sNumVal
END

select col1, dbo.udf_StripNum (LTRIM (RTRIM (col1))) from numtest

----------


## JBane

Good use of a function!

Jeff

----------


## sleezy

jeff :- 

Ran the first script and it works well.Thanks but why does it keep a length of 15 long? Please explain the 
( '0', 13- len(col1)) part.Its making the field 15 long instead of 13. 

update numtest
Set col1 = rtrim(ltrim(col1)) + replicate( '0', 13- len(col1)) 
where len(rtrim(ltrim(col1))) < 13

Results 

1#$234(*)5!!saf 	000000000012345
1234567890123   	001234567890123

Part two The Function
 Thanks its great. Just had to change the @sVal varchar (15)) to 13 and it works. Thanks guys my eyes have been opened.

----------


## JBane

Sleeze,
I simply used a 15 length column in my example, if you table has a max length of 13, then you would remove:


CASE
WHEN substring(col1, 14,1) LIKE '[0-9]'
THEN substring(col1, 14,1) ELSE '' END +
CASE
WHEN substring(col1, 15,1) LIKE '[0-9]'
THEN substring(col1, 15,1) ELSE '' END 

from the update statement. I like Andi's function better because you only need change the 13 to 15 as you have done. This statement:

set col1 = rtrim(ltrim(col1)) + replicate( '0', 13- len(col1)) 

Uses REPLICATE to append the zeros onto the end of the number:

REPLICATE (<this character>, <this many times>)

13 - len(col1) determines how many characters less than 13 the column is using. So:

Select 'HELLO' + replicate('!', 5) will return:

HELLO!!!!!

By taking the length of the number and subtracting it from 13, we now know how many zeros to add:

select col1 + replicate( '0', 13- len(col1)) 

Hope that clarifies,


Jeff

----------


## sleezy

Thanks l now understand.Thanks mate...

----------

