order by problem

[bruttas]

Hi all,

I need help with an order by issue.

I query my data for a list of images and I tell it order by name.

It find 11 images for our example and it comes out like this.....

CAR-1.jpg

CAR-10.jpg

CAR-11.jpg

CAR-2.jpg

CAR-3.jpg

CAR-4.jpg

CAR-5.jpg

CAR-6.jpg

CAR-7.jpg

CAR-8.jpg

CAR-9.jpg

I need it to be arranged like this...

CAR-1.jpg

CAR-2.jpg

CAR-3.jpg

CAR-4.jpg

CAR-5.jpg

CAR-6.jpg

CAR-7.jpg

CAR-8.jpg

CAR-9.jpg

CAR-10.jpg

CAR-11.jpg

As always thanks for any help.

Brian

[Adam Cameron.]

G'day

When asking a DB question, it helps answer it if you tell us what DB you're using.

You're gonna need to somehow create a column that has only the numeric part of that string, and order by that.

This'll involve using whatever string functions your DB offers to to a find / substr or a regex substitution.  The reason I am vague here is because this is where it's important for you to identify which DB you're using before people will be able to answer your questions thoroughly.

I suspect this will not be a very performant operation, so rearranging your table structure to store that value when the row is inserted / updating might be a better approach, if performance is a concern.

--

Adam

[bruttas]

Hi Adam,

Im using Microsoft SQL Server 2005

[Adam Cameron.]

Right.  All the docs are online, so you can look 'em up yerself.  There'll be a section on string functions... you need to find T-SQL's equivalent of find(), substr() / mid().  SQL Server's regex support is a bit "minimal" from memory (I think perhaps in the latest version it catches up to everything else a bit), so you'll need to use find() to find the "-", and the ".", and the substr() / mid() function to extract the number in between.  Then chuck the result in an INT column, and do a ORDER BY on that.

Make sense?

--

Adam

[Dan_Bracuk]

In sql server, charindex() will give you the positions of the hyphens and periods.  Then you use substring() to get the part in between, and cast() to convert that substring to the number you want to use in your order by clause.

As Adam said, all your reference material is available on the internet.

[BKBK]

You could do something similar to this:

select quality, owner, name

from imageTBL

where len(name) = 9

order by name

union

select quality, owner, name

from imageTBL

where len(name) = 10

order by name

Mind you, I am making the assumption that the database engine will respect the order of the 2 result sets. Some might, others might not.