How to remove leading zeros?

Question

How to remove leading zeros?

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

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?

No, the PATINDEX finds the beginning of 'ed' in the column--meaning, the index of the 'e'.

The -1 is just to subtract one from that value, so that the LEFT function will get everything up to the 'e', not everything up to and including the 'e'. But there could be a thousand characters after 'ed' and it would still work.

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

quote:
No, the PATINDEX finds the beginning of 'ed' in the column--meaning, the index of the 'e'.
The -1 is just to subtract one from that value, so that the LEFT function will get everything up to the 'e', not everything up to and including the 'e'. But there could be a thousand characters after 'ed' and it would still work.

But if 'ed' isnt in the string then the LEFT function is giving an error, since patindex will return 0. Doing a LEFT(str, -1) gives the error. yes?

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

I think DavidT got it...

LEFT JOIN DataPaqMFG ON convert(varchar(15),(replicate('0', (15 - len(DataPaqMFG.TD_PART))))) + cast(DataPaqMFG.TD_PART as varchar(15)) = convert(varchar(15),(replicate('0', (15 - len(products.PART_NUM))))) + cast(products.PART_NUM as varchar(15))

Can anyone find a reason this would fail? It seems to work for me.

Thanks again

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

Hi CarKnee,

you've already seem to have found a solution.

What about REPLACE (your_column, 'ed','',1) and CAST this as int?

Haven't tried this here at home, but when it works, it should be faster because of less elementar operations

Cheers,

Frank

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

spongemagnet SSCrazy Points: 2812 More actions · Answer 5

quote:
Hi CarKnee,
you've already seem to have found a solution.
What about REPLACE (your_column, 'ed','',1) and CAST this as int?
Haven't tried this here at home, but when it works, it should be faster because of less elementar operations
Cheers,
Frank

Hrmmm.. In none of the examples did I see an actual zero, other than the leading zeros. Why not just do a replace on the zero's to nothing?

from table1

left outer join table2

on replace(table1.col1,'0','') = table2.col1

I'm making a big assumption though.

-Ken

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

quote:
Hrmmm.. In none of the examples did I see an actual zero, other than the leading zeros. Why not just do a replace on the zero's to nothing?
from table1
left outer join table2
on replace(table1.col1,'0','') = table2.col1
I'm making a big assumption though.

looking again at the provided sample data, that's a ggod point!

Looks fairly simpler than

LEFT JOIN DataPaqMFG ON convert(varchar(15),(replicate('0', (15 - len(DataPaqMFG.TD_PART))))) + cast(DataPaqMFG.TD_PART as varchar(15)) = convert(varchar(15),(replicate('0', (15 - len(products.PART_NUM))))) + cast(products.PART_NUM as varchar(15))

Let's hope it works on all data

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

There can be zeros in the middle of the number. I just didnt give it as an example.

Thanks.