T-SQL ORDER BY number and letters mixed in string
I have a series of unique strings that contain numbers, and sometimes numbers and a letter whose sample reads:
- 1X
- 2X
- 2Y
- 12X
- 20
- 21
The number / s always precedes a letter. What is the ORDER BY clause (T-SQL) clause for creating a list that will give me the order as shown above?
I tried to use
LEN (fieldName), field name - this will work, but for 20 and 21. I tried to express the strings as an integer, but CAST failed during conversion.
+3
source to share
1 answer
I stole my data from here .
declare @t table(s varchar(25))
insert @t
select '122345684XT' union
select '23339034300-XT' union
select '423432424523242332X' union
select '422222222111111111232' union
select '423842389034209XYZ' union
select 'ABC'
select
left(s,patindex('%[^0-9]%',S+' ')-1 ) nbr
,right(s,len(s)-patindex('%[^0-9]%',S+' ')+1) alpha
from @t
that leads to
122345684 XT
23339034300 -XT
422222222111111111232
423432424523242332 X
423842389034209 XYZ
ABC
To use it in your context.
SELECT *
FROM YourTable
ORDER BY left(s,patindex('%[^0-9]%',S+' ')-1 ),
right(s,len(s)-patindex('%[^0-9]%',S+' ')+1)
Shown
declare @t table(s varchar(25))
insert @t
select '12X' union
select '1X' union
select '2X' union
select '2Y' union
select '20' union
select '21'
SELECT *
FROM @t
ORDER BY CAST(left(s,patindex('%[^0-9]%',S+' ')-1 ) AS INT),
right(s,len(s)-patindex('%[^0-9]%',S+' ')+1)
Results in
1X 2X 2Y 12X 20 21
+2
source to share