471,072 Members | 1,243 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,072 software developers and data experts.

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 64594
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.