Sorting nvarchar column as integer

问题:

I have mixed data i nvarchar column (words and numbers). Which is fastest way to sort data in this column in Numeric Order.

Result example:

• 1
• 2
• 3
• ...
• 10
• 11
• ...
• aaaa
• aaab
• b
• ba
• ba
• ...

回答1:

Use this:

ORDER BY
    CASE WHEN ISNUMERIC(column) = 1 THEN 0 ELSE 1 END,
    CASE WHEN ISNUMERIC(column) = 1 THEN CAST(column AS INT) ELSE 0 END,
    column

This works as expected.

---

Note: You say fastest way. This sql was fast for me to produce, but the execution plan shows a table-scan, followed by a scalar computation. This could possibly produce a temporary result containing all the values of that column with some extra temporary columns for the ISNUMERIC results. It might not be fast to execute.

回答2:

If you left pad your numbers with 0's and sort on that, you will get your desired results. You'll need to make sure that the number of 0's you pad with matches the size of the varchar column.

Take a look at this example...

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('1')
Insert Into @Temp Values('2')
Insert Into @Temp Values('3')
Insert Into @Temp Values('10')
Insert Into @Temp Values('11')
Insert Into @Temp Values('aaaa')
Insert Into @Temp Values('aaab')
Insert Into @Temp Values('b')
Insert Into @Temp Values('ba')
Insert Into @Temp Values('ba')

Select * From @Temp
Order By Case When IsNumeric(Data) = 1 
              Then Right('0000000000000000000' + Data, 20) 
              Else Data End

Also note that it is important when using a case statement that each branch of the case statement returns the same data type, or else you will get incorrect results or an error.

回答3:

--check for existance
if exists (select * from dbo.sysobjects where [id] = object_id(N'dbo.t') AND objectproperty(id, N'IsUserTable') = 1)
drop table dbo.t
go

--create example table
create table dbo.t (c varchar(10) not null)
set nocount on

--populate example table
insert into dbo.t (c) values ('1')
insert into dbo.t (c) values ('2')
insert into dbo.t (c) values ('3 ')
insert into dbo.t (c) values ('10 ')
insert into dbo.t (c) values ('11')
insert into dbo.t (c) values ('aaaa')
insert into dbo.t (c) values ('aaab')
insert into dbo.t (c) values ('b')
insert into dbo.t (c) values ('ba')
insert into dbo.t (c) values ('ba')

--return the data
select c from dbo.t
order by case when isnumeric(c) = 1 then 0 else 1 end,
case when isnumeric(c) = 1 then cast(c as int) else 0 end,
c

回答4:

You can either treat the data as alphanumeric, or numeric, not both at the same time. I don't think what you're trying to do is possible, the data model isn't set up appropriately.

回答5:

I don't think what you're trying to do is possible

This example works fine

SELECT * FROM TableName
ORDER BY CASE WHEN 1 = IsNumeric(ColumnName) THEN Cast(ColumnName AS INT) END

Result is:

• a
• b
• c
• ...
• 1
• 2
• 3

But i need numbers first.

回答6:

This should work :

select * from Table order by ascii(Column)

回答7:

Cast it.

SELECT * FROM foo ORDER BY CAST(somecolumn AS int);

Been a while since I've touched SQL Server, so my syntax might be entirely incorrect though :)