| Author |
Topic |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2007-11-08 : 10:35:31
|
| Hi All,I have a table called Table1.I have a (nvarchar) field called ProductID. The records in this field is numeric. The length of each record in this field is different. In my select statement I want to get the ProductID field in a way that each field length becoms 13 by adding leading zero's. If the ProductID length is alrerady 13 then the SELECT should not add leading zero's.So for example if the ProductID was 1234567890 the SELECT will return 0001234567890Is there a function that I can use in my select to do this?Thanks in adavnace.Zee |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-08 : 10:36:42
|
right('0000000000000' + ProductID, 13)
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-08 : 10:39:11
|
orstuff(ProductID, 1, 0, replicate('0', 13 - len(ProductID)))
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2007-11-08 : 10:55:32
|
| Thanks guys,On thing I noticed that the ProductID may have leading spaces or spaces in the end.For example look at the following (I have put bracket so that it will be easy to understand).ProductID[ 1234567890 ]In the above example the length is 13. There are 2 empty spaces in the begining and 1 emopty space in the end which makes the total length of 13. I want to get rid of the spaces and then put leading zero's to make it 13 lenght(if its not already 13 length).So I guess I need to do the following:a) Remove the spaces (only spaces that are in the begining and/or in the end).b) If the lenght is not 13 put leading zero's to make the length 13.How can i do that in my SELECT statement?Thanks in advance.Zee |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-08 : 11:05:37
|
RIGHT(REPLICATE('0', 13) + LTRIM(RTRIM(ProductID)), 13)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2007-11-08 : 11:28:04
|
| Peso,Thanks..it looks good.But isn't the following also correct?right('0000000000000' +ltrim(rtrim(ProductID)) , 13)Please comment. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-08 : 11:31:59
|
same. REPLICATE('0', 13) will generate a string with 13 '0'
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2007-11-08 : 11:43:03
|
| Thank you all for quick help :) |
|
|
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2007-11-08 : 11:46:10
|
| One more thing.What if I want to return all the ProductID which has space in the begining or in the end. That is I dont want to put zero's but only to see ProductID's which has space in the begining or in the end.Any idea?Thanks in advance.Zee |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-08 : 12:29:20
|
[code]where left(ProductID, 1) = ' 'or right(ProductID, 1) = ' '[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
Adding leading zero's to make the field length 13