CODEWITHSUNDEEP
sqlsql-serversql-order-by
Ibraheem Mansour
Ibraheem Mansour

Reputation: 99

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.

Upvotes: 1

Views: 96

Answers (5)

Igor Micev
Igor Micev

Reputation: 1662

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]

Upvotes: 2

Shabi Levi
Shabi Levi

Reputation: 258

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";

Upvotes: 1

Ajay2707
Ajay2707

Reputation: 5808

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

Upvotes: 1

Hyperion
Hyperion

Reputation: 588

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 ;)

Upvotes: 1

Chetan Sanghani
Chetan Sanghani

Reputation: 2111

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.

Upvotes: 1

Related Questions