CODEWITHSUNDEEP
sqlsql-servert-sql
user3486647
user3486647

Reputation: 311

How to sort a varchar column that contains numbers and letters in SQL Server?

I have a varchar column that contain numbers (1-99999) and letters (AM0001-BF9999).

Since it has letters so i can't just convert it to int.

Is there a way to maybe use grouping_id to sort this column by numbers (small to large) then follow by letters (alphabetically)?

Thanks..

Upvotes: 2

Views: 3100

Answers (3)

Stefan Steiger
Stefan Steiger

Reputation: 82136

That's what you get when you denormalize your database schema.
Prefix and number should be stored separately.
That said, this is what I did when I had the same problem: 

SELECT * FROM YOUR_TABLE
ORDER BY dbo.GetNumbers(YOUR_FIELD), YOUR_FIELD




Create Function dbo.GetNumbers(@Data VarChar(8000))
Returns int
AS
Begin   
    Return CAST(Left(
             SubString(@Data, PatIndex('%[0-9.-]%', @Data), 8000), 
             PatIndex('%[^0-9.-]%', SubString(@Data, PatIndex('%[0-9.-]%', @Data), 8000) + 'X')-1) AS int)
End

See also this post for extracting numbers from strings
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server/

Upvotes: 0

King King
King King

Reputation: 63317

You can try using the ISNUMERIC function like this:

select * from test_table
order by 
     case isnumeric(test_column) 
     when 1 then convert(int,test_column)
     else 999999 end, test_column

Sql fiddle demo.

Upvotes: 1

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

You need to know what the maximum length of your field is. Assuming 25 characters for illustrative purposes, this will work:

select
    v
from (
    select 
        right(space(25) + v,25) as v
    from ( values
         ('1-99999')
        ,('AM0001-BF9999')
    ) data(v)
)data
order by v

to yield:

v
-------------------------
                  1-99999
            AM0001-BF9999

Upvotes: 1

Related Questions