Click here to Skip to main content
15,074,394 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
CBSFBE20151202000017_000_1.tif
CBSFBE20151202000017_000_10.tif
CBSFBE20151202000017_000_100.tif
CBSFBE20151202000017_000_101.tif
CBSFBE20151202000017_000_102.tif
CBSFBE20151202000017_000_103.tif
CBSFBE20151202000017_000_104.tif
CBSFBE20151202000017_000_105.tif
CBSFBE20151202000017_000_106.tif
CBSFBE20151202000017_000_107.tif
CBSFBE20151202000017_000_108.tif
CBSFBE20151202000017_000_109.tif

What I have tried:

I have to sort an alphanumeric value containing some special characters in sql server, I have tried several order by clause but it is not giving the desired output( It is giving the output as 1,10,100 than 101 it should be 1,2,3..100 )
I have tried ordering by alphabets and number at same time by split but it didn't worked.
Posted
Updated 26-Jul-18 5:30am

1 solution

Basically, you've stored it wrong: you should consider changing it to three fields and a computed column to "regenerate" the full name. Why? Because SQL is not good at string handling and you need to break up the date, convert part of it to an integer, and then use that as the sort value - or you will never get a "proper" sort.

Alternatively, change the names so they are all "zero padded":
CBSFBE20151202000017_000_0001.tif
CBSFBE20151202000017_000_0010.tif
CBSFBE20151202000017_000_0100.tif
CBSFBE20151202000017_000_0101.tif
CBSFBE20151202000017_000_0102.tif
But that generally impacts a lot of systems and is more trouble than it's worth.

YOu can do what you want, but ... you need to:
1) Break out the file name to remove everything after teh '.' - that's CHARINDEX and LEFT
2) Extract the part after the last '_' character - that's more complicated: Is there a LastIndexOf in SQL Server? - Stack Overflow[^] will show you how.
3) Convert the bit you have left - a string based number - to an integer, and use that as your ORDER BY clause.

Complicated? Yes. Nasty? Yes. Have to do it every time you want to do this? Yes.
See what I mean about "you've stored it wrong"?
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900