Post reply

How to remove leading zeros?

  • July 25, 2003 at 7:16 am

    #80916

    I have to do a LEFT JOIN on two tables. Both fields are the same type (nvarchar) but one table has leading zeros in it.

    The records look like this in one table 00123 and 123 in the other. However, there are a few instance where it is 00123ed of 123ed. Therefore CASTing them to int will not work for all records.

    Any help is appreciated!

    Cheers,

    CarKnee

  • July 25, 2003 at 7:26 am

    #467037

    Hi Carknee,

    quote:

    The records look like this in one table 00123 and 123 in the other. However, there are a few instance where it is 00123ed of 123ed. Therefore CASTing them to int will not work for all records.

    is it coincidence in your example that in both case you use 'ed' or is this a constant?

    BTW, not the best prerequisites for a join, I think

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 25, 2003 at 7:33 am

    #467042

    Hi I see three alternatives depending on your requirements on speed/data-integrity.

    Alternative 1.

    If you are using MSSQL2K then you could create a user defined function to strip leading zeroes. (See BOL on how to create functions.)

    SELECT ...

    FROM TableA a

    LEFT JOIN TableB b on dbo.fn_stripZeroes(a.column) = b.column

    This method will suffer in performance though.

    Alternative 2

    If the leading zeroes are not required then I would suggest to remove them from the database.

    If they are needed by some client you could add them via a view quite easy

    Alternative 3

    If performance is an issue you could create another column in the table which contains the same value as the one you're performing the join on but with the leading zeroes removed.

    This method will however lead to redundace and you will have to make sure that both columns get updated.

    /Fredrik

  • July 25, 2003 at 7:39 am

    #467046

    The fields will have the same value...

    12345 -- 12345

    00123 -- 123

    01234 -- 1234

    01ed3 -- 1ed3

    The only cases that have letters are "ed". It is roughly 200 records out of 200,000 that have letters in them.

    It is SQL 7.0; no user defined functions can be used.

    Unfortunetely, I do not have full control over the database. I am the lowely web programmer and I need to work with what is given. 🙁

    From what I am told is that they wipe out the records on an almost daily basis and re-import them from a distributor from a flat text file!

    Thanks all.

  • July 25, 2003 at 7:46 am

    #467048

    How about using the length of the second column a the integer value in a right expression?

    select a.col1...

    from table_a left join table_b

    on right(table_a.col1,length(table_a.col1)) = table_b.col1

    ....

    If you have any trailing spaces you would need to rtrim the columns.

    Performance might not be good as I don't think it would use indexes but you might have other columns to use in the join which might improve performance.

    Jeremy

  • July 25, 2003 at 7:56 am

    #467051

    Hi,

    Is the column len fixed in table a?

    If so then you could construct the join like this:

    SELECT *

    FROM TableA a

    LEFT JOIN TableB b ON a.col = RIGHT('00000' + b.col, 5)

    Eg add zeroes to the table that doesn't have them.

    /Fredrik

  • July 25, 2003 at 7:58 am

    #467053

    Hi CarKnee,

    quote:

    The only cases that have letters are "ed". It is roughly 200 records out of 200,000 that have letters in them.

    in this case, you might take a look at PATINDEX in BOL to extract 'ed' and then CONVERT or CAST to int.

    Sorry, I haven't got the time to figure out, Maybe someone else could do this!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 25, 2003 at 8:02 am

    #467066

    Jeremy,

    Good call! It works!

    Here is the statement:

    LEFT JOIN DataPaqMFG ON RTRIM(DataPaqMFG.TD_PART) = RIGHT(RTRIM(products.PART_NUM), LEN(RTRIM(DataPaqMFG.TD_PART)))

    Thanks All!

  • July 25, 2003 at 8:12 am

    #467073

    It should not work (depending on what your data look like;-) since 123 in DataPaqMFG will be matched against 00123 and 02123 in products.

    /Fredrik

  • July 25, 2003 at 8:16 am

    #467075

    this should do it:

    cast(left(annoying_column,patindex('%ed%',annoying_column) - 1) as integer)

    you can either cast the other column as an integer, or throw another cast around this whole thing to get it back to character data.

  • July 25, 2003 at 8:20 am

    #467078

    quote:

    this should do it:

    cast(left(annoying_column,patindex('%ed%',annoying_column) - 1) as integer)

    you can either cast the other column as an integer, or throw another cast around this whole thing to get it back to character data.

    juppdiwupp, but this assumes only one number following 'ed', right?

    Hope you do not need this dynamically!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 25, 2003 at 8:31 am

    #467084

    Frehan,

    Good point. I will look at your solution to add zeros to the column without them!

    Sam,

    If ED isnt found then I am trying to do a LEFT with a -1.

  • July 25, 2003 at 8:41 am

    #467090

    Both fields in both tables have the same type and length. nvarchar 15

    Can I add a dynamic amount of zeros? For instance... If the string is 123 can I add 12 zeros, and if the string is 12345 can I add 10 zeros?

    C

  • July 25, 2003 at 8:55 am

    #467096

    dynamically add zeros:

    declare @String varchar(15)

    set @String = '123'

    select convert(varchar(12),(replicate('0', (12 - len(@String))))) + cast(@String as varchar(12))

    returns 000000000123 etc..

  • July 25, 2003 at 8:56 am

    #467097

    Replace varchar(12) to varchar(15) in last post.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply