Skip to content
Advertisement

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

Advertisement

Answer

This should work

select name_subagent
from Subagent
order by CAST(LEFT(name_subagent, PATINDEX('%[^0-9]%', name_subagent + 'a') - 1) as int)
6 People found this is helpful
Advertisement