sorting nvarchar fields

Hi all,

I am having an issue which I'm guessing everyone has at one point. I just can't seem to find the answer. I might be misunderstanding something as I'm relatively new to SQL and data types etc.

Here is my issue.

I have a table with a column that has a nvarchar data type. The following is an example of field values in that column.

asystem1
zsystem19
zsystem9
bsystem23
bsystem3

I would like to sort that data alphabetically and then by number like so.

asystem1
bsystem3
bsystem23
zsystem9
zsystem19

A basic ORDER BY obviously will sort it alphabetically but will put 23 before 3. These system names are already named and can't be changed otherwise I would use "0" in front of single digit numbers.

I just can't figure out how to accomplish this. Is there a convert or cast (unfamiliar with those functions) that I can do or does it involve something more sophisticated?

I appreciate any help in advance.

Israel

Use CAST:

CAST ( expression AS data_type )

OR CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Quote:

---

Originally Posted by israelnyc

Hi all,

I am having an issue which I'm guessing everyone has at one point. I just can't seem to find the answer. I might be misunderstanding something as I'm relatively new to SQL and data types etc.

Here is my issue.

I have a table with a column that has a nvarchar data type. The following is an example of field values in that column.

asystem1
zsystem19
zsystem9
bsystem23
bsystem3

I would like to sort that data alphabetically and then by number like so.

asystem1
bsystem3
bsystem23
zsystem9
zsystem19

A basic ORDER BY obviously will sort it alphabetically but will put 23 before 3. These system names are already named and can't be changed otherwise I would use "0" in front of single digit numbers.

I just can't figure out how to accomplish this. Is there a convert or cast (unfamiliar with those functions) that I can do or does it involve something more sophisticated?

I appreciate any help in advance.

Israel

---

That much I know. What do I cast as or convert to though to get those specific results?

I know I can't convert to numeric because I've tried that and get an error.

Unfortunately, I can't think of a nice, elegant solution. If the numbers always occur at the end of the name, then here is an ugly solution:

ORDER BY
CASE WHEN PATINDEX('%[0-9]%', YourColumn) > 0 THEN LEFT(YourColumn, PATINDEX('%[0-9]%', YourColumn) -1) ELSE YourColumn END,
CONVERT(int, CASE WHEN PATINDEX('%[0-9]%', YourColumn) > 0 THEN SUBSTRING(YourColumn, PATINDEX('%[0-9]%', YourColumn), LEN(YourColumn)) ELSE '' END)

Thank you very much, that worked for values with numbers on the end.

I don't think I will need it for values with numbers in random parts of the value but just out of curiousity, how difficult would that be to code?

It could be done - it would just make the CASE statement bigger and uglier.

OK thanks. I'll play around with it.