Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Hi Adam,
Im using Microsoft SQL Server 2005
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.