Select query with length issue

Question

Possible Duplicate:
Most efficient T-SQL way to pad a varchar on the left to a certain length?

I have the customer table where the customerID is of length:10 but there are few customer where the length is 3 or 5

but the output should be like the following, I need to prefix zero here if length is less than 10

Script to get this data is:

select customerID from customer

Joe G Joseph · Accepted Answer · 2012-10-01T08:47:26.270

2

Try the following code:

Since you are using sql server 2008, you could use replicate function to add zeros to the value..

Select ltrim(right(replicate(0,10) + <column>,10)) 
from your_table

edited Oct 01 '12 at 08:47

answered Oct 01 '12 at 08:37

Joe G Joseph

this will avoid necessity of counting zeros, will be slower though. – dejjub-AIS Oct 01 '12 at 08:40
@NoobASThreeDeveloper: slowness due to replicate is negligible .. :) – Joe G Joseph Oct 01 '12 at 08:48

score 1 · Answer 2 · answered Oct 01 '12 at 08:35

1

You can use in this way...

SELECT RIGHT('0000000000' + RTRIM('3445'), 10)

In your case,

SELECT RIGHT('0000000000' + RTRIM(customerID), 10) AS New_CustomerID
FROM Customer

answered Oct 01 '12 at 08:35

Abdul Ahad

score 0 · Answer 3 · answered Oct 01 '12 at 08:35

0

Try this

SELECT RIGHT('0000000000'+`customerID`, 10) FROM `customer`

answered Oct 01 '12 at 08:35

Bunjerd Sparrow

3 Answers3