How to remove leading zeros?

Question

How to remove leading zeros?

CarKnee

SSC-Addicted

Points: 402
More actions
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

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

frehan SSChasing Mays Points: 653 More actions · Answer 2

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

CarKnee SSC-Addicted Points: 402 More actions · Answer 3

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.

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 4

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

frehan SSChasing Mays Points: 653 More actions · Answer 5

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

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]

CarKnee SSC-Addicted Points: 402 More actions · Answer 7

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!

frehan SSChasing Mays Points: 653 More actions · Answer 8

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

Sam Ryda SSC Rookie Points: 26 More actions · Answer 9

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.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

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]

CarKnee SSC-Addicted Points: 402 More actions · Answer 11

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.

CarKnee SSC-Addicted Points: 402 More actions · Answer 12

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

DavidT SSCommitted Points: 1727 More actions · Answer 13

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

DavidT SSCommitted Points: 1727 More actions · Answer 14

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