473,224 Members | 1,770 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 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 65474
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

43
by: steve | last post by:
I am quite frustrated with phps include, as I have spent a ton of time on it already... anyone can tell me why it was designed like this (or something I dont get)? The path in include is...
17
by: Nollie | last post by:
Say you have a struct: struct MYSTRUCT { int x; int y; int w; int h; };
5
by: Hendrik Schober | last post by:
Hi, we just run into the problem, that "default" alignment in the project properies dialog seem to be different. We have a project that's a DLL, which is linked with a couple of LIBs. All are...
5
by: Dan C Douglas | last post by:
I have just installed VS.NET 2003 on my computer. I have a project that I have been developing on VS.NET 2002. I haven't upgraded this project to VS.NET 2003 yet and I am still developing it in...
43
by: markryde | last post by:
Hello, I saw in some open source projects a use of "!!" in "C" code; for example: in some header file #define event_pending(v) \ (!!(v)->vcpu_info->evtchn_upcall_pending & \...
4
by: jason.awlt | last post by:
Greetings, I recently being bugged by the following error on my DB2. SQL0902C SQLSTATE = 58005 Reason Code = 14 This error comes out everytime i tried to insert some record to a table...
11
by: walterbyrd | last post by:
My MySQL table has a field that is set as type "date." I need to get today's date, and insert it into that field. The default for that MySQL field is 2006-00-00. I know about the date()...
0
by: BBHKLAM | last post by:
Can someone help out on this problem we have or someone encouter this simiar issue? We are replicated data from MVS DB2 to Oracle 10G. Lately we often getting sql1476 with sqlcode "-430". I open...
6
by: nickyazura | last post by:
hello, i would like to know how to do numbering format for "0000" where each time it will generate 0001,0002,0003 and so on untill 9999. counter = "0000" counter = counter + 1
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

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.