Ibraheem Mansour Ibraheem Mansour - 4 months ago 7
SQL Question

SQL Order By in and varchar

I want to order a column of type nvarchar that contains integers and strings. I want to order first the integers ASC then the strings.
This is my SQL statement:

SELECT DISTINCT [Password]
FROM OCPR
WHERE [Password] IS NOT NULL
ORDER BY Password


The result is
1, 10, 11, 12, 2, 3, K1, K2, N


I want
1, 2, 3, 10, 11, 12, K1, K2, N

Answer
SELECT DISTINCT [Password] 
FROM OCPR 
WHERE [Password] IS NOT NULL
ORDER BY
    CASE WHEN ISNUMERIC(Password) = 1 THEN 0 ELSE 1 END,
    CASE WHEN ISNUMERIC(Password) = 1 THEN CAST(Password AS INT) ELSE 0 END,
    Password

plz try this.