Home › Forums › General Discussions › Open Topic › sql assistance
- This topic has 12 replies, 3 voices, and was last updated 18 years, 7 months ago by
Anthony.
-
AuthorPosts
-
January 6, 2004 at 2:45 pm #46602
AnthonyParticipant<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
400I want to have the results displayed like this:
001
002
010
035
070
100
200
300
400If drawing numbers <10 then add 00
if drawing numbers <100 then add 0what is the correct syntax for this?
asp.
January 6, 2004 at 4:18 pm #98231
Bucky RamoneParticipantKnow that there’s a nice trick for it, will look it up at work tomorrow….
January 6, 2004 at 8:13 pm #98232
jeremiahKeymasterare 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?January 6, 2004 at 9:32 pm #98233
AnthonyParticipantim just displaying
3 char field? how to set up ?
im hoping to just apply some sort of format with an ‘if’ statement to achieve
January 6, 2004 at 9:43 pm #98234
jeremiahKeymasterYou could try something like this:
intNumber = 1
strResult = Right("00" & intNumber, 3) ‘ gives 001intNumber = 35
strResult = Right("00" & intNumber, 3) ‘ gives 035intNumber = 200
strResult = Right("00" & intNumber, 3) ‘ gives 200January 7, 2004 at 3:28 am #98235
Bucky RamoneParticipantNice 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 –> 200January 7, 2004 at 6:44 am #98236
jeremiahKeymasterYours 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?
January 7, 2004 at 7:08 am #98237
AnthonyParticipantso 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>
January 7, 2004 at 7:11 am #98238
AnthonyParticipantit is access 97 data
January 7, 2004 at 7:25 am #98239
Bucky RamoneParticipant"jeremiah" wrote:Yours was even better DBdoes 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
January 7, 2004 at 7:36 am #98240
jeremiahKeymaster"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 functionLOL –
if only more packages would adhere to standards my life would easier for sure!
January 7, 2004 at 8:12 am #98241
Bucky RamoneParticipantOne of our sqlserver guys came with this solution:
Right$("000" & cstr(intNumber),3)
…which looks remarkably alike to Jeremiah’s suggestion….
January 7, 2004 at 10:40 am #98242
AnthonyParticipantthanks for the input guys.
i went with the last example i posted.
-
AuthorPosts
You must be logged in to reply to this topic.
Share:
- Click to email a link to a friend (Opens in new window)
- Click to share on Twitter (Opens in new window)
- Click to share on Tumblr (Opens in new window)
- Click to share on Facebook (Opens in new window)
- Click to share on Reddit (Opens in new window)
- Click to share on Pocket (Opens in new window)
- Click to share on WhatsApp (Opens in new window)
- Click to share on Pinterest (Opens in new window)
- Click to share on Telegram (Opens in new window)
- Click to share on Skype (Opens in new window)