Order By mix alphanumeric numeric

Question

Desired sort order with SQL server 2005:

 

N93540107

 

 

Y2685T107

 

 

Y8564M105

 

 

000361AG0

 

 

000361AH8

 

 

0010EQAB2


Any suggestions appreciated. Reviewed some solutions but didn't work. Adding 0 to left, doing something with the len function, etc. tried in vain.

Answer 1

Can you provide the table structure and some data?

---

Abdallah, PMP, ITIL, MCTS

Answer 2

If I take a look at your data Are you wish to sort it by alphabet then by numeric?

declare @table table (data nvarchar(100))

insert into @table (data)
SELECT 'N93540107' UNION
SELECT 'Y2685T107' UNION
SELECT 'Y8564M105' UNION
SELECT '000361AG0' UNION
SELECT '000361AH8' UNION
SELECT '0010EQAB2'


SELECT data from @table
order by data

would produce where the data with 0 came out first cause it's in numeric (ascii is smaller 0 = 060 and N =116)

try this

DECLARE @table1 table (ID int identity(1,1), data nvarchar(100))

INSERT INTO @table1 (data)
SELECT Data FROM @table
WHERE LEFT(Data,1) like '[A-Z]'
OR LEFT(Data,1) like '[a-z]'
ORDER BY data


INSERT INTO @table1 (data)
SELECT Data FROM @table
WHERE LEFT(Data,1) like  '[0-9]'
ORDER BY data


SELECT DATA FROM @Table1
ORDER BY ID

---

Best Regards,
Melissa Suciadi

If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

Answer 3

Can you tell us the logic for the order?

Try using the "case" function.

SELECT
    c1
FROM
    (
    SELECT 'N93540107' AS c1 UNION ALL
    SELECT 'Y2685T107' UNION ALL
    SELECT 'Y8564M105' UNION ALL
    SELECT '000361AG0' UNION ALL
    SELECT '000361AH8' UNION ALL
    SELECT '0010EQAB2'
    ) AS T
ORDER BY
    CASE WHEN c1 LIKE '[a-zA-Z]%' THEN 1 ELSE 2 END,
    c1;
GO

AMB

Answer 4

I'll try these options as currently I am stuck in some other tasks assigned. Will update you as soon as I can.

The logic was that first all alphabets, then all numeric. The sublogic being with the 2 sets, it goes by ascii   value character by character, resulting in 400 comes before 45.

Thanks a lot to all contributors