Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Petronas
Posting Yak Master
134 Posts |
 Posted - 2011-05-06 : 21:15:13
|
| Hello,I have to remove leading 0's in the zip code and just display the first 5 characters after removing the 0's. I am using the following to get rid of the leading 0's.Replace(Ltrim(Replace(c.zip, '0', ' ')), ' ', '0')as zipI don't know how do I show just the 5 characters after removing the zeros. Sometimes there is 1 or 2 or 3 leading zeros. Thanks,Petronas. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-06 : 22:12:30
|
If there are fewer than 5 characters after you remove the leading zeros, do you still want to display 5 characters by padding on the left with zeros? If so you could simply do this:right('00000'+c.zip,5) as zipIf you don't want to do that, i.e., if you are left with fewer than 5 characters, and you just want to display whatever is left, then you can just add a right function to your query, as inright(Replace(Ltrim(Replace(c.zip, '0', ' ')), ' ', '0'),5)as zip All of this assumed that if there are more than 5 characters left after you strip off the zeros, you want to display the 5 characters on the right. If it is the l5 characters on the left that you want to display, then your logic would change slightly and you would be using the left function to trim-off the extra trailing characters. |
 |
|
|
Petronas
Posting Yak Master
134 Posts |
Posted - 2011-05-06 : 22:51:36
|
| Thank you so much. It worked ! Appreciate your help |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-07 : 08:19:37
|
| You are quite welcome!Just to be sure, your zip codes would not have any spaces in them, would they? I know Canadians use zip codes with spaces, so if there is a possibility of such zip codes, you will need to change the way you strip off the leading zeros. |
|
|
|
|
|
|
|
|
Removing leading zeros in the zip code