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 |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2009-10-07 : 08:25:23
|
| Hi All,I have a table called 'Table1' with the following field.ProductID (varchar 50 length)The records are like this. There is no particular length.ProductID002345687600000041000000000012444In my SELECT query I want to remove the leading zero's only. But the SELECT should never return a 0, instead a blank ''.So for the above the SELECT should return the following.ProductID_Modified234568760041012444How to achieve that in a SELECT query. For some specific reasons I do not want a CASE statement please. Thanks for all the help.Zee |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-07 : 08:55:24
|
select replace('#'+convert(varchar(10),convert(int,ProductID)),'#0',' ')
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-07 : 08:59:36
|
[code]select coalesce(convert(varchar(10), nullif(convert(int, ProductID), 0)), '')[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-07 : 09:07:56
|
OK - my solution isn't working proper!I'm on the way with a "no java script" browser in the moment so I cannot delete my post.Maybe later.Sorry
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-07 : 10:04:56
|
quote:
Originally posted by zeeshan13
Hi All,I have a table called 'Table1' with the following field.ProductID (varchar 50 length)The records are like this. There is no particular length.ProductID002345687600000041000000000012444In my SELECT query I want to remove the leading zero's only. But the SELECT should never return a 0, instead a blank ''.So for the above the SELECT should return the following.ProductID_Modified234568760041012444How to achieve that in a SELECT query. For some specific reasons I do not want a CASE statement please. Thanks for all the help.Zee
Where do you want to show the data?MadhivananFailing to plan is Planning to fail |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-10-07 : 10:12:58
|
| I want to show an use the data at the back end within SQL Server. I do not use front end application. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-07 : 10:26:36
|
| Ok. Another methodselect SUBSTRING(productid,patindex('%[1-9]%',productid+'1'),LEN(productid)) from your_tableMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Removing leading zeros, should not return 0