-
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 ] )
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|