how to custom sort data in sql server

Assumption: Values are always of the format 'Letter-Alphanumeric string'

Try this:

select card_no
from [Coll2012-13]
order by left(card_no,1),
case
when isnumeric(substring(card_no,3, len(card_no))) = 1
then cast(substring(card_no,3, len(card_no)) as int)
else cast(substring(card_no,3, patindex('%[A-Z]%',substring(card_no,3, len(card_no)))-1) as int)
end,
case 
when patindex('%[A-Z]%', substring(card_no,3,len(card_no))) > 0
then substring(card_no,patindex('%[A-Z]%', substring(card_no,3,len(card_no)))+2,1)
end

How this works: First check the starting letter. Next, check if the alphanumeric part is in fact only numeric. If so, get the integer value of that part. If it is not, get the numeric part of it and use that as the sort value. Finally, if the alphanumeric part does contain a letter, use that as another sort value.

Demo here.

You should replace all non digit chars with "" and convert the rest in a number. Then sort by the number.

You can try to use a function as described here SQL Server 2000: how do I return only the number from a phone number column for this

A simple - but 'should be maintained' - solution is to create a sorting table and join that table to your result set. The table contains all CardNo values and associates a sort order to them.

EDIT:

CREATE TABLE CardNoOrderHelper (
    CardNo VARCHAR(16)
    , OrderRank INT CONSTRAINT DF_CardNoOrderHelper_OrderRank DEFAULT 0
    , PRIMARY KEY CLUSTERED (
        CardNo ASC
    )
);

-- Fill your table with the expected sort order (dinstinct insert, than adjust the order ranks)

SELECT
    *
FROM
    [Coll2012-13] AS T
    LEFT JOIN CardNoOrderHelper CH
        ON T.CardNo = CH.CardNo
ORDER BY
    T.OrderRank

As I said, you have to maintain this table. When your resultset is small this can be done manually.

Try this

with cte
AS
(
    select 
       * 
       ,substring(CardNo, 1, charindex('-')-1) RealRank1
       ,substring(CardNo, charindex('-')+ 1, 10) RealRank2
    from 
       [Coll2012-13] 
)
select 
    *
from 
    cte
order by
    RealRank1
    ,RealRank2

I have the similar problem. I have used this query below.

To use this query u must know the ID (string) maximum length and I have adjusted the format in this query to use Mark-Number+Alphabet (example R-1,R-1A, R-11, R-11A, R-1AA,R-1B)

Query:

select 
 b.CardNo, b.separatorIndex
 ,b.Mark, b.Mark_length
 ,case b.isNumericMark1 + b.isNumericMark2 + b.isNumericMark3
   when 1 then cast (b.Mark1 as int)
   when 2 then cast (b.Mark1 + b.Mark2 as int) 
   when 3 then cast (b.Mark1 + b.Mark2 + b.Mark3 as int) 

  end as Mark1
from
(
    select 
        a.CardNo
        ,charindex('-',a.cardNo,0) as separatorIndex
        , len(a.cardNo) - charindex('-',a.cardNo,0) as Mark_length
        , substring(a.CardNo,0,charindex('-',a.cardNo,0)) as Mark

        , substring(a.CardNo,charIndex('-',a.cardNo,0)+1,1) as Mark1
        , isnumeric(substring(a.CardNo,charIndex('-',a.cardNo,0)+1,1*1)) as isNumericMark1

        , substring(a.CardNo,charIndex('-',a.cardNo,0)+2,1) as Mark2
        , isnumeric(substring(a.CardNo,charIndex('-',a.cardNo,0)+2,1)) as isNumericMark2

        , substring(a.CardNo,charIndex('-',a.cardNo,0)+3,1) as Mark3
        , isnumeric(substring(a.CardNo,charIndex('-',a.cardNo,0)+3,1)) as isNumericMark3

    from [Coll2012-13] a
) b
order by Mark,Mark1,Mark_length

Result:

CardNo               separatorIndex Mark                 Mark_length Mark1
-------------------- -------------- -------------------- ----------- -----------
R-1                  2              R                    1           1
R-1A                 2              R                    2           1
R-2                  2              R                    1           2
R-2B                 2              R                    2           2
R-3                  2              R                    1           3
R-12                 2              R                    2           12

Hope this help.