Printable View
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
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
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
Good use of a function!
Jeff
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.
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
Thanks l now understand.Thanks mate...