How to sort alphanumeric string in SQL Server

i want to sort开发者_JAVA技巧 an alphanumberic string...

sample string :

D12,D13
F19,F20
A12,A13
F10,F11
D14
A1,A2
A5,A6
D4,D5
F5,F6,F7

Desired Output :

A1,A2
A5,A6
A12,A13
D4,D5
D12,D13
D14
F5,F6,F7
F10,F11
F19,F20

HELP ME !!!!

---

Based on new requirements and loosely on @kuru kuru's answer, I hope this order by clause demonstrates why normalization is a good thing. Just because it's good for JSON and Ajax doesn't mean it's how you should treat your database:

DECLARE @t TABLE (x VARCHAR(32));

INSERT @t VALUES
  ('D12,D13'),
  ('F19,F20'),
  ('A12,A13'),
  ('F10,F11'),
  ('D14'),
  ('A1,A2'),
  ('A5,A6'),
  ('D4,D5'),
  ('F5,F6,F7'),
  ('AA1,AA2'),
  ('Z98,Z99');

SELECT x FROM @t
ORDER BY CASE 
    WHEN UPPER(x) LIKE '[A-Z][A-Z]%'
    THEN 'Z' + LEFT(x,2) ELSE LEFT(x,1) END,
    CONVERT(INT, 
      CASE WHEN x LIKE '%,%' THEN 
        SUBSTRING(x, PATINDEX('%[0-9]%', x), 
        CHARINDEX(',', x)-PATINDEX('%[0-9]%', x))
      ELSE 
        SUBSTRING(x, PATINDEX('%[0-9]%', x), 32)
      END
    );

---

As @JNK and @Aaron Bertrand have mentioned, T-SQL is not really the best choice for this task.

Having said that, there are a couple of issues to tackle.

1. You're going to have to separate each item into two components, the alpha part and the numeric part, otherwise there is no way "A5,..." is going to be sorted before "A12,...", since "A5" comes after "A1" when comparing string values. You'll have to parse the values and turn "A5" into "A05" (or however many significant digits you need) in order to get a correct sort order.

2. Are you ignoring the additional item(s) after the comma in each string? If the answer is no, then you'll have to do the processing described in #1 on each value in the string. For example, is it possible to have "A5, A6" and you want that value sorted before "A5, A12"?

Once you have normalized the values in the strings, then you can tackle the comparison and sorting.

---

I can't believe everyone's saying all those mean things about TSQL. :-)

You don't have to normalize the data before you sort it. (Well, not explicitly in any case -- the SQL engine can just wear some beer-goggles when it processes the ORDER BY clause, and it will work fine).

Here's a sample that works in TSQL... I know it looks like there's no reason for the right side to order properly, but it does actually order properly.

declare @table TABLE (item varchar(10))
insert into @table(item) values('FF5')
insert into @table(item) values('Z10')
insert into @table(item) values('F15')
insert into @table(item) values('F20')
insert into @table(item) values('A7')
insert into @table(item) values('A12')

SELECT
    item
FROM
    @table
ORDER BY
    CASE WHEN SUBSTRING(item,2,1) LIKE '[A-Z]' THEN LEFT(item,2) ELSE LEFT(item,1) END