CODEWITHSUNDEEP
sqlsql-server-2008
BernieSF
BernieSF

Reputation: 1828

Order by only the numeric part of a string

I have a column called Number populated with this data (column is nchar):

1
2
1091
3
20
2B

I want a select statement that give this order:

1
2
2B
3
20
1091

How I can accomplish this? Thanks everybody for your help

(EDITED)

Upvotes: 6

Views: 7635

Answers (3)

Yaroslav
Yaroslav

Reputation: 6534

Use PATINDEX, check this SQL Fiddle code. @AaronBertrand just answered as I was testing.

Just to add some more value and info, check this SQLServerCentral link as there you can find more answers.

And check this Natural (human alpha-numeric) sort in Microsoft SQL 2005, lot of useful answers and variations.

Upvotes: 1

Jeremy J Starcher
Jeremy J Starcher

Reputation: 23863

What you are looking for is called a NATURAL SORT, which is different from the regular sort that most systems give. I've found a handful of posts here at Stack Overflow that covers natural sorts in various popular SQL engines, including one brief discussion about SQL Server.

The ideas here may get you started on a solution.

Oracle: How can I implement a "natural" order-by in a SQL query?

You might have to change the syntax for your SQL engine though.

Found another version for MYSQL:

Natural Sort in MySQL

Can't find any for SQL Server yet.

Edit3

Ah, this one covers some SQL Server ideas too:

Natural Sort in MySQL

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

You can perform some tricks by converting to a numeric after you discover the location of the first non-numeric. Appending a random character at the end makes it treat all strings the same even if the original string did not contain an alphabetic.

SELECT [Number] FROM dbo.TableName 
ORDER BY CONVERT(INT, LEFT(Number, PATINDEX('%[^0-9]%', Number + 'z')-1));

Upvotes: 18

Related Questions