"Order by" doesn't work with characters and numeric 2

[d222222]

I have a program where the user can create a folder name but when it is in alphabetical order and there is a number in it, it doesn't sort in the right order:

Folder Name 1
Folder Name 22
Folder Name 4
New Folder 1
New Folder 2

If the prefix was always the same I could do it but since they can give it any name I can't get it to work.

The "Folder Name 22" should come after "Folder Name 4".

How can I get it to be in the correct order?


Thanks.

 

[ESquared]

order by
left(name, patindex('%[0-9]%') - 1), Right('0000000000' + substring(name, patindex('%[0-9]%'), 11), 11)

further modification is possible if numbers are ever followed by more letters

 

[haroldholt]

Many thanks, this has now been added to my ever-expanding SQL expressions toolbox

left(name, patindex('%[0-9]%',name) - 1), Right('0000000000' + substring(name, patindex('%[0-9]%',name), 11), 11)

 

[ESquared]

oops, left out some operands there, sorry about that (was going at high speed!)

 

[d222222]

I get this error message when I try your code:

Invalid length parameter passed to the substring function.

The folder names can be anywhere from 2-20 characters long. You mention that it can be modified if there are more letters but there can be more or less letters. How can it be changed to work for all of the folder names no matter how long they are?


Thanks.

 

[ESquared]

I didn't say anything about simply more letters. I mentioned if there are more letters following the numbers.

I neglected to put in the column name that patindex was supposed to operate on, so use the modification haroldholt already posted:

left(name, patindex('%[0-9]%', name) - 1), Right('0000000000' + substring(name, patindex('%[0-9]%', name), 11), 11)

This works with any number of total characters.
It works with up to 11 digits for the folder number.
It won't work correctly if any letters come AFTER the numbers:

Folder Name 22
Folder Name 4
Folder Name 4a
-->
Folder Name 00000000004
Folder Name 00000000022
Folder Name 0000000004a <-- 4a doesn't come after 4

It won't work correctly if the folder name has a number in it:

Do this 1st 11
Do this 1st 2
-->
Do this 0000001st 2
Do this 000001st 11

(This is the same problem as "any letters follow the first number.")

 

[d222222]

Thank you for your response.

I did use the edited code that haroldholt submitted and that's when I got the error message. I just tried it again and I still get it.

Since I was getting an error message about an invalid length parameter I thought it had to do with the length of the folder name but according to your response that doesn't seem to be the problem.

This is what I am trying:

SELECT Folders.* , DocID, AppUserID, [Filename], ContentType, ByteSize, FileBinary, [TimeStamp] From Folders LEFT OUTER Join FileInfo on Folders.FolderID = FileInfo.FolderID ORDER BY left(Folders.FolderName, patindex('%[0-9]%', Folders.FolderName) - 1), Right('0000000000' + substring(Folders.FolderName, patindex('%[0-9]%', Folders.FolderName), 11), 11)

I even tried it directly in the query analyzer and got the same error message.

 

[ESquared]

Oh, got it. Some of your folders names don't have numbers after them. I should have thought of that!

left(name, patindex('%[0-9]%', name [b][COLOR=black yellow]+ '0'[/color][/b]) - 1), Right('[b][COLOR=black yellow]0[/color][/b]0000000000' + substring(name, patindex('%[0-9]%', name [b][COLOR=black yellow]+ '0'[/color][/b]), 11), 11)

The changes are highlighted.

 

[d222222]

Thank you sooooooo much!! It works!!!