Results 1 to 7 of 7

Thread: Strip field of Num numeric characters

  1. #1
    Join Date
    Sep 2002
    Posts
    218

    Strip field of Num numeric characters

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    181
    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

  3. #3
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    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

  4. #4
    Join Date
    Dec 2002
    Posts
    181
    Good use of a function!

    Jeff

  5. #5
    Join Date
    Sep 2002
    Posts
    218

    Thanks Guys

    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.

  6. #6
    Join Date
    Dec 2002
    Posts
    181
    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

  7. #7
    Join Date
    Sep 2002
    Posts
    218
    Thanks l now understand.Thanks mate...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •