dcsimg
Results 1 to 7 of 7

Thread: sorting nvarchar fields

  1. #1
    Join Date
    May 2007
    Posts
    10

    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

  2. #2
    Join Date
    Jun 2007
    Posts
    1
    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

  3. #3
    Join Date
    May 2007
    Posts
    10
    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.

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    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)

  5. #5
    Join Date
    May 2007
    Posts
    10
    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?

  6. #6
    Join Date
    Dec 2004
    Posts
    502
    It could be done - it would just make the CASE statement bigger and uglier.

  7. #7
    Join Date
    May 2007
    Posts
    10
    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
  •