CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ariana
ariana

Reputation: 33

Sort a VARCHAR column in SQL Server that contains numbers?

I have a column in which data has letters with numbers.

For example: 

1 name
2 names .... 
100 names

When sorting this data, it is not sorted correctly, how can I fix this? I made a request but it doesn’t sort correctly.

select name_subagent
from Subagent
order by 
    case IsNumeric(name_subagent) 
        when 1 then Replicate('0', 100 - Len(name_subagent)) + name_subagent
        else name_subagent
end

enter image description here

Upvotes: 1

Views: 1130

Answers (2)

Ariz
Ariz

Reputation: 46

This should work

select name_subagent
from Subagent
order by CAST(LEFT(name_subagent, PATINDEX('%[^0-9]%', name_subagent + 'a') - 1) as int)

Upvotes: 2

Jose Bagatelli
Jose Bagatelli

Reputation: 1409

This expression will find the first occurrence of a letter withing a string and assume anything prior to this position is a number. You will need to adapt this statement to your needs as apparently your data is not in Latin characters. With a bit of tweaking you should be able to achieve exactly what you're looking for:

select 
    name_subagent
from 
    Subagent
order by 
    CAST(SUBSTRING(name_subagent,0,PATINDEX('%[A-Z]%',name_subagent)) as numeric)

Note, the '%[A-Z]%' expression. This will only look for the first occurrence of a letter within the string.
I'm not considering special characters such as '!', '#' and so on. This is the bit you might want to play around with and adapt to your needs.

Upvotes: 0

Related Questions