Sort Order Problem -- Seems simple, but isn't

# Thread: Sort Order Problem -- Seems simple, but isn't

1. Senior Member
Join Date
Dec 1969
Posts
241

## Sort Order Problem -- Seems simple, but isn't

I am having trouble with my sorting... <BR><BR>declare @orderme table( <BR>sortme varchar(100) <BR>) <BR><BR>insert into @orderme <BR>select &#039;test 1&#039; <BR>union <BR>select &#039;test 2&#039; <BR>union <BR>select &#039;test 10&#039; <BR>union <BR>select &#039;apples 1&#039; <BR>union <BR>select &#039;apples 2&#039; <BR>union <BR>select &#039;apples 10&#039; <BR><BR>select sortme from @orderme <BR>order by sortme <BR><BR>produces <BR><BR>apples 1 <BR>apples 10 <BR>apples 2 <BR>test 1 <BR>test 10 <BR>test 2 <BR><BR>i need it to produce<BR> <BR>apples 1 <BR>apples 2 <BR>apples 10 <BR>test 1 <BR>test 2 <BR>test 10 <BR><BR>"apples" and "test" could be any text string at all... <BR><BR>HELP! <BR><BR>(I have BoL&#039;d and google&#039;d and I can&#039;t find anything to help me, any pointers much appreciated)

2. Senior Member
Join Date
Dec 1969
Posts
16,931

## That's completely right.

"10" as a string comes before "2" as a string, if ordered alphabetically.<BR><BR>Is "apples 1" one database column? Can it ever contain something different, like "apples or oranges 1"? What about "apples 1 oranges 1"?<BR><BR>If not, you could use the string-handling functions (BOL) to get the first space character, and extract the second bit and convert it to a number. Then you&#039;re ordering by the first bit as a string, ascending, followed by the second bit as a number, ascending...<BR><BR>If not, I think you&#039;re going to have problems.<BR><BR>Craig.

3. Senior Member
Join Date
Dec 1969
Posts
241

## RE: That's completely right.

I think I am going to have problems as well.<BR><BR>The column is basically free format text (products) and we need to produce a picking list (e.g. physically in the warehouse the products are ordered nicely on the shelves to make it easy for the warehouse people to pack)<BR><BR>a1<BR>a10<BR>b1<BR>b2<BR>b13<BR>p1<BR >p2<BR>p10<BR><BR>with a picking list of<BR><BR>a1<BR>a10<BR>b1<BR>b13<BR>b2<BR>p1<BR>p1 0<BR>p2<BR><BR>it takes forever to pack anything, and as this is a (^^(*&^ big warehouse and a picking list can be (^&^* big it is not possible to reorder the warehouse to fit the fact that a database wont order text in the same way a human does...<BR><BR>I also can not guarentee where the number is going to be... (first space, second space, no space...)<BR>

4. Senior Member
Join Date
Dec 1969
Posts
16,931

## RE: That's completely right.

Well...<BR><BR>Maybe you can get around it. The only reason "10" is in front of "2" is because "2" should be "02".<BR><BR>You could write a trigger or stored procedure or something that, whenever a record is added to the database, it searches that string and finds any numeric sections and pads them to a certain number of characters (changes "2" to "0002" and "12" to "0012" or something)...<BR><BR>Wouldn&#039;t be particuarly easy, but it would probably work... It really does depend what the REAL strings are like. So far you&#039;ve posted "apples 1" and "a1" - what are they REALLY like - post some examples.<BR><BR>Craig.

5. Senior Member
Join Date
Dec 1969
Posts
241

## RE: That's completely right.

Cheers for that Craig, its&#039; given me something to think about...<BR><BR>I can probably do the number mangling on the application side (ASP) and remangle on the output for display on the ASP side as well....

#### Posting Permissions

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