468,253 Members | 1,296 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,253 developers. It's quick & easy.

SQL Format Function "0000"

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

Feb 12 '07 #1
3 63346
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

Feb 12 '07 #2
....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
Feb 12 '07 #3
On 12 fv, 22:17, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
...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

Feb 13 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

43 posts views Thread by steve | last post: by
5 posts views Thread by Hendrik Schober | last post: by
5 posts views Thread by Dan C Douglas | last post: by
43 posts views Thread by markryde | last post: by
4 posts views Thread by jason.awlt | last post: by
11 posts views Thread by walterbyrd | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.