CODEWITHSUNDEEP
sql-servert-sqlsorting
Rakesh
Rakesh

Reputation: 2790

Sort Alphanumeric values in T-SQL ,SQL Server

Hi I have values like this in my field name called "NumberName"

AB1
CD2
XH506
PQ104
PZ77

I am trying to order this so far I have tried is:

select * from view_name where NumberName='Something' 
order by RIGHT('0000' + SUBSTRING(NumberName, ISNULL(NULLIF(PATINDEX('%[0-9]%',NumberName), 0), 
LEN(NumberName)+1), LEN(NumberName)), 4)

and

order by LEN(NumberName),NumberName

how to achieve this..?

Upvotes: 0

Views: 194

Answers (3)

Devart
Devart

Reputation: 122042

Perhaps, I didn't fully understand your question. But ORDER BY NumberName correctly sorts the values.

Query:

DECLARE @temp TABLE(NumberName NVARCHAR(50))

INSERT INTO @temp (NumberName)
VALUES ('AB1'),('CD2'),('XH506'),('PQ104'),('PZ77')

SELECT *
FROM @temp
ORDER BY NumberName

Output:

NumberName
----------
AB1
CD2
PQ104
PZ77
XH506

Upvotes: 1

AdamL
AdamL

Reputation: 13191

You didn't explain this well enough, but I recon you'd like to sort the result based on value of rightmost number. Try this. You need to first find first occurence of not-a-number form the right, to know where the last number begins (PATINDEX('%[0-9]% will give you first number, but in "FB3C2" for example numbers are mixed with letters).

select 
    * 
    ,right(NumberName,PATINDEX('%[A-Z]%',reverse(NumberName))-1)
from (
    select 'ABC001' as numbername union all
    select 'XQ20001' union all
    select 'XQ20002' union all
    select 'XQ20003' union all
    select 'XQ20004' union all
    select 'PM130010' union all
    select 'PM130011' union all
    select 'PM130012' union all
    select 'PM130013' union all
    select 'PM130014' union all
    select 'PM130015' union all
    select 'FB3C2' union all
    select 'FB3C2' union all
    select 'PM13001' union all
    select 'PM13001' union all
    select 'PM13002'
) x
order by 
    cast(right(NumberName,PATINDEX('%[A-Z]%',reverse(NumberName))-1) as int)

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13506

select * from yourtable
order by LEFT(columnname,1)

Upvotes: 1

Related Questions