Sort nvarchar column as a whole

Question

I have mixed data i have nvarchar column (words and numbers). The fastest way to sort the data in this column in numerical order.

Example result:

• 1
• 2
• 3
• ...
• 10
• eleven
• ...
• aaaa
• AAAB
• b
• ba
• ba
• ...

Answer 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 the fastest way. This sql was fast for me, but the execution plan shows a table scan and then a scalar calculation. This can lead to a temporary result containing all the values ​​of this column with some additional temporary columns for the ISNUMERIC results. This may not be fast to complete.

Answer 2

0 , . , 0, , varchar.

...

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

, case , case , .

Answer 3

- ( * dbo.sysobjects, [id] = object_id (N'dbo.t ') objectproperty (id, N'IsUserTable') = 1)
  drop table dbo.t

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

-
dbo.t(c) ('1')
dbo.t(c) ('2')
dbo.t(c) ('3')
dbo.t(c) ('10 ')
dbo.t(c) ('11')
dbo.t(c) ('aaaa')
dbo.t(c) ('aaab')
dbo.t(c) ('b')
dbo.t(c) ('ba')
dbo.t(c) ('ba')

-
c dbo.t
, isnumeric (c) = 1, 0 else 1 end,
case, isnumeric (c) = 1, cast (c as int) else 0 end,
c

Answer 4

You can process data as alphanumeric or numeric, rather than simultaneously. I do not think that what you are trying to do is perhaps the data model is not configured accordingly.

Answer 5

I do not think that you are trying to make it possible

This example works great

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

Result:

• and
• b
• with
• ...
• 1
• 2
• 3

But first I need the numbers.

Answer 6

This should work:

select * from Table order by ascii(Column)

Answer 7

Throw it.

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

It has been a while since I touched SQL Server, so my syntax may be completely wrong :)