SQL Format Function "0000"

[duprejonathan]

Hello, i don't know how to format a string with an SQL select query
for my VB6 App.

I have a table like this :
Code - Name
1 - Jonathan
2 - Mike
....
9 - Claudia
10 - Robbie
11 - Sandy

But I would get code column result's with a particular format like
this :

0001 - Jonathan
0002 - Mike
....
0009 - Claudia
0010 - Robbie
0011 - Sandy

I use the Format(column, "#0000") function in my application ffor the
moment but nothing to do with the DB Engine side ???

I tried CONVERT function :
SELECT CONVERT(varchar(4), code, '0000') FROM Employes;
But the code result's stil 1,2,3 and not 0001,0002,0003 !!!

Anyone has the solution ?

Thanks

Jonathan

[Plamen Ratchev]

Hi Jonathan,

Probably it is better to leave this formatting on the VB side as it is more
powerful there. But if you have to do it on the DB side, here are two ways:

SELECT REPLICATE('0', 4 - DATALENGTH(CAST(1 as varchar))) + CAST(1 as
varchar), RIGHT(CAST('0000' + CAST(1 as varchar) as varchar), 4)

In your case it will be like:

SELECT REPLICATE('0', 4 - DATALENGTH(CAST(code as varchar))) + CAST(code as
varchar), RIGHT(CAST('0000' + CAST(code as varchar) as varchar), 4)
FROM Employes

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

....it has been a long day already :)

On the last one you do not need the extra CAST:

SELECT RIGHT('0000' + CAST(1 as varchar), 4)

or,

SELECT RIGHT('0000' + CAST(code as varchar), 4) FROM Employes

Plamne Ratchev
http://www.SQLStudio.com

[duprejonathan]

On 12 fév, 22:17, "Plamen Ratchev" <[e-mail]:

...it has been a long day already :)

On the last one you do not need the extra CAST:

SELECT RIGHT('0000' + CAST(1 as varchar), 4)

or,

SELECT RIGHT('0000' + CAST(code as varchar), 4) FROM Employes

Plamne Ratchevhttp://www.SQLStudio.com

Thank you very much !!!!!!! Its works.

Jonathan