sql assistance

[Anthony]

<Recordset1>

That will plop the records from column "Drawing Number" from Recordset1.

the data in that column are numbers
1
2
10
35
70
100
200
300
400

I want to have the results displayed like this:
001
002
010
035
070
100
200
300
400

If drawing numbers <10 then add 00
if drawing numbers <100 then add 0

what is the correct syntax for this?

asp.

[Bucky Ramone]

Know that there’s a nice trick for it, will look it up at work tomorrow….

[jeremiah]

are you just displaying the results or storing them in a table afterwords?
If you are just diplaying it you could cast it into a 3 char field for that purpose or do a select as and set the format?

[Anthony]

im just displaying

3 char field? how to set up ?

im hoping to just apply some sort of format with an ‘if’ statement to achieve

[jeremiah]

You could try something like this:

intNumber = 1
strResult = Right("00" & intNumber, 3) ‘ gives 001

intNumber = 35
strResult = Right("00" & intNumber, 3) ‘ gives 035

intNumber = 200
strResult = Right("00" & intNumber, 3) ‘ gives 200

[Bucky Ramone]

Nice one, Jeremiah….

This should also do the trick:

to_char(intNumber,"009")

the "009" is the format which returns the leading zeroes. If you use the format "999", it will supress the leading zeroes.

intNumber = 1 –> 001
intNumber = 35 –> 035
intNumber = 200 –> 200

[jeremiah]

Yours was even better DB

does to_char work on sqlserver? I’ve never tried it there, just with oracle. Very cool.

Just thought about this – what type of db is this anthony?

[Anthony]

so is this one

<%IF (Recordset1.Fields.Item("Drawing Number").Value) <10 THEN Response.Write "00" ELSE IF (Recordset1.Fields.Item("Drawing Number").Value) <100><Recordset1>

[Anthony]

it is access 97 data

[Bucky Ramone]

"jeremiah" wrote:

Yours was even better DB

does to_char work on sqlserver? I’ve never tried it there, just with oracle. Very cool.

Thanks

I am not too familiar with sqlserver, but according to me ‘to_char’ is a standard sql function

[jeremiah]

"dB stands for den Buck" wrote:

"jeremiah" wrote:

I am not too familiar with sqlserver, but according to me ‘to_char’ is a standard sql function

LOL –

if only more packages would adhere to standards my life would easier for sure!

http://www.zdnet.com.au/builder/architect/database/story/0,2000034918,20264748,00.htm”>http://www.zdnet.com.au/builder/archite … 748,00.htm

[Bucky Ramone]

One of our sqlserver guys came with this solution:

Right$("000" & cstr(intNumber),3)

…which looks remarkably alike to Jeremiah’s suggestion….

[Anthony]

thanks for the input guys.
i went with the last example i posted.

[Author]

Posts