SQL Order By in and varchar

Question

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.

Storing the password in a plain-text field is a horrible idea, if this is your case, please check [Best way to store password in database](http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database). — Mincong Huang, Jul 11 '16 at 10:15

score 2 · Answer 1 · answered Jul 11 '16 at 16:16

ISNUMERIC doesn't answer the question you think it does. For instance, ISNUMERIC('£') will return 1, but you'll get an error trying to convert that to int. – Damien_The_Unbeliever

Create this function in your database which is better than the in-built ISNUMERIC(), and always exact!

CREATE FUNCTION dbo.IsAllDigits (@MyString VARCHAR(8000))
RETURNS TABLE AS
RETURN (
         SELECT CASE 
                WHEN @MyString NOT LIKE '%[^0-9]%'
                THEN 1
                ELSE 0
                END AS IsAllDigits
        )

Then use this code for example:

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

score 1 · Accepted Answer · answered Jul 11 '16 at 10:04

1

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.

answered Jul 11 '16 at 10:04

Chetan Sanghani

2,058
2
21
36

1

`ISNUMERIC` doesn't answer the question you think it does. For instance, `ISNUMERIC('£')` will return 1, but you'll get an error trying to convert that to `int`. – Damien_The_Unbeliever Jul 11 '16 at 10:14
As I say, try running your sample code where `Password` just contains the `£` sign. You'll get an error. – Damien_The_Unbeliever Jul 11 '16 at 10:22
@Damien_The_Unbeliever : ohk. getting you – Chetan Sanghani Jul 11 '16 at 10:23
@Damien_The_Unbeliever : But in Ibraheem case , there aren't any special characters so it works fine. – Chetan Sanghani Jul 11 '16 at 10:26
@ChetanSanghani - it was just a single example. There are plenty of others - e.g. `'1d1'` will also fall over here. As will `'.'`. The point is, the function answers a question nobody wants to ask and so any use of it is almost always a sign of breakage. – Damien_The_Unbeliever Jul 11 '16 at 10:39
@Damien_The_Unbeliever : Thanks, I am getting so much from you. Now onward I will take care of all this things. – Chetan Sanghani Jul 11 '16 at 10:41

Ajay2707 · Answer 3 · 2016-07-11T10:21:36.367

You can use Patindex for this

Declare @Tasks table(id [nvarchar](max) NOT NULL)

insert into @tasks values ('1'),('7'),('2'), ('12'),('23'),('10'),('K1'), ('K2'), ('P'), ('N')

SELECT * FROM @tasks
ORDER BY 
CASE 
  WHEN ISNUMERIC(id)=1 
  THEN CAST(id as int)

  WHEN PATINDEX('%[^0-9]%',id) > 1 
  THEN CAST(
    LEFT(
      id,
      PATINDEX('%[^0-9]%',id) - 1
    ) as int)

  ELSE 2147483648
END, 


CASE 
  WHEN ISNUMERIC(id)=1 
  THEN NULL

  WHEN PATINDEX('%[^0-9]%',id) > 1 
  THEN SUBSTRING(
      id,
      PATINDEX('%[^0-9]%',id) ,
      50
    ) 

  ELSE id
END

Shabi Levi · Answer 4 · 2016-07-11T10:23:24.797

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

/////////////////////////////////////////////////////////

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

$query_test = "SELECT DISTINCT OCPR.password FROM OCPR WHERE OCPR.password IS NOT NULL ORDER BY OCPR.password";

score 1 · Answer 5 · answered Jul 11 '16 at 10:10

1

you could do it using the ISNUMERIC function. Your query would look like this:

SELECT DISTINCT [Password] 
FROM OCPR 
WHERE [Password] IS NOT NULL
ORDER BY 
CASE WHEN ISNUMERIC([Password])=1 THEN CONVERT(int,[Password]) ELSE 99999999 END,
[Password]

Hope this helped ;)

answered Jul 11 '16 at 10:10

Hyperion

568
4
5

`ISNUMERIC` doesn't answer the question you think it does. For instance, `ISNUMERIC('£')` will return 1, but you'll get an error trying to convert that to `int`. – Damien_The_Unbeliever Jul 11 '16 at 10:14

SQL Order By in and varchar

5 Answers5