SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Adding leading zero's to make the field length 13
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/08/2007 :  10:35:31  Show Profile  Reply with Quote
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 0001234567890

Is there a function that I can use in my select to do this?

Thanks in adavnace.

Zee

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 11/08/2007 :  10:36:42  Show Profile  Reply with Quote
right('0000000000000' + ProductID, 13)


KH
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 11/08/2007 :  10:39:11  Show Profile  Reply with Quote
or
stuff(ProductID, 1, 0, replicate('0', 13 - len(ProductID)))


KH
Time is always against us

Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/08/2007 :  10:55:32  Show Profile  Reply with Quote
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



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 11/08/2007 :  11:05:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
RIGHT(REPLICATE('0', 13) + LTRIM(RTRIM(ProductID)), 13)



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/08/2007 :  11:28:04  Show Profile  Reply with Quote

Peso,
Thanks..it looks good.

But isn't the following also correct?

right('0000000000000' +ltrim(rtrim(ProductID)) , 13)

Please comment.

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 11/08/2007 :  11:31:59  Show Profile  Reply with Quote
same. REPLICATE('0', 13) will generate a string with 13 '0'


KH
Time is always against us

Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/08/2007 :  11:43:03  Show Profile  Reply with Quote
Thank you all for quick help :)
Go to Top of Page

zeeshan13
Constraint Violating Yak Guru

USA
347 Posts

Posted - 11/08/2007 :  11:46:10  Show Profile  Reply with Quote

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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 11/08/2007 :  12:29:20  Show Profile  Reply with Quote
where left(ProductID, 1) = ' '
or    right(ProductID, 1) = ' '



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000