Ordering Problem with Varchar

# Thread: Ordering Problem with Varchar

1. Junior Member
Join Date
Dec 1969
Posts
9

## Ordering Problem with Varchar

Hello,<BR>I&#039;m hoping someone can help me out. I currently have a table with a field of varchar(100). It currently holds Printer Models.<BR><BR>Here is some example data..<BR><BR>tbl_Models<BR>ID - ModelNumber<BR>1 - B140<BR>2 - 2005<BR>3 - 12<BR>4 - B300<BR>5 - B31<BR>6 - 1120<BR><BR>When I perform a query against the table and order by the ModelNumber. It gives me this...<BR><BR>1120<BR>12<BR>2005<BR>B140<BR>B300< BR>B31<BR><BR>Now, I would like these results returned the way humans are used to searching for things...<BR><BR>12<BR>1120<BR>2005<BR>B31<BR>B140 <BR>B300<BR><BR>Thanks in advance for any help,<BR>Jose

2. Senior Member
Join Date
Dec 1969
Posts
11,247

## Nasty nearest I can think is something like

SELECT ModelNumber From &#060;table&#062; ORDER BY <BR><BR>IIF ( ISNUMERIC ( ModelNumber ), RIGHT ( ( &#039;000&#039; + ModelNumber ), 100 ), ModelNumber ) <BR><BR>Where &#039;000&#039; should actually be 100 characters of 0

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## That's for Access DB...

...but I don&#039;t think it does the full job, anyway.<BR><BR>Suppose you have<BR> B6<BR> B12<BR> B110<BR><BR>Even with that, you would end up with the ORDER BY giving you<BR> B110<BR> B12<BR> B6<BR><BR>In general, this isn&#039;t a solvable problem. But within limits, you can do pretty well.<BR><BR>For example, you could look to see if all characters *after* the first are numeric, and the rebuild the ORDER BY based on that.<BR><BR>And then look to see if there are TWO non-numeric leading characters.<BR><BR>But you have to set a reasonable upper limit on the process.<BR><BR>It would have been better to have split the ModelNumber into TWO fields in the DB: An alphabetic prefix and a numeric suffix. That still might be the best option.<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Since you said Varchar...

...and not text, I&#039;m assuming that you are using some DB *other* than Access. If so, which?<BR><BR>Also, are there ever model numbers with more than one leading alphabetic character?<BR><BR>

5. Junior Member
Join Date
Dec 1969
Posts
9

## RE: Since you said Varchar...

Yes,<BR>A model could have 2 words in it. For example<BR><BR>Stylus 345E, etc.<BR><BR>Thanks,<BR>Jose

6. Junior Member
Join Date
Dec 1969
Posts
9

## RE: That's for Access DB...

Bill,<BR>Thanks. Yes, it seems unsolvable. I told my Boss that it might not be possible, so it won&#039;t be a big deal. Thanks for your help.<BR><BR>Jose

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## I think you are hosed.

So if you had<BR> Stylus 345E<BR> Stylus 47X<BR> Stylus 9J<BR>they would get listed in that order.<BR><BR>In order to "fix" this, you&#039;d have to go in and "normalize" all your numbers to be the same length. You could do that with *EITHER* zeroes or spaces:<BR> Stylus 009J<BR> Stylus 047X<BR> Stylus 345E<BR>or<BR> Stylus&nbsp;&nbsp;&nbsp;9J [3 spaces]<BR> Stylus&nbsp;&nbsp;47X [2 spaces]<BR> Stylus&nbsp;345E [1 space]<BR><BR><BR><BR>

8. Junior Member
Join Date
Dec 1969
Posts
9

## RE: I think you are hosed.

Bill,<BR>That is exactly what I am doing. I added a new field, that I will order by. This field will have the Model Number with the extra zeros. Thanks,<BR>Jose

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•