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..?
select * from yourtable
order by LEFT(columnname,1)
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)
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
