I have a requirement where I have to apply sort in an SQL query by an Alphanumeric column such that the values in it get sorted like the following:
AD
AH
AK1
B1
B2
B3x
B3y
B11
C1
C6
C12x
UIP
instead of the default way:
AD
AH
AK1
B1
B11
B2
B3x
B3y
C1
C12x
C6
UIP
I was able to write the following SQL statement:
SELECT [MyColumn]
FROM [MyTable]
ORDER BY
LEFT([MyColumn], PATINDEX('%[0-9]%', [MyColumn]) - 1),
CONVERT(INT, SUBSTRING([MyColumn], PATINDEX('%[0-9]%', [MyColumn]), LEN([MyColumn]) - (PATINDEX('%[0-9]%', REVERSE([MyColumn]))))),
RIGHT([MyColumn], PATINDEX('%[0-9]%', [MyColumn]) - 1)
I just need to know how to implement this in a conditional way such that this doesn't get triggered for alpha-only value else it would give error, or if there is a way to make it work with that as well?
P.S. The environment is SQL Server 2014.
It is a bit of a mess to look at and I agree with Sean Lange that you should look into recording this in more than one field, but you can use case
expressions within your order by
to have conditional ordering:
declare @t table(c nvarchar(10));
insert into @t values('AD'),('AH'),('AK1'),('B1'),('B2'),('B3x'),('B3y'),('B11'),('C1'),('C6'),('C12x'),('UIP');
select c
from @t
order by
case when patindex('%[0-9]%', c) = 0
then c
else left(c, patindex('%[0-9]%', c) - 1)
end
,case when patindex('%[0-9]%', c) = 0
then 0
else convert(int,substring(c,patindex('%[0-9]%', c),len(c) - (patindex('%[0-9]%', reverse(c)))))
end
,right(c,case when patindex('%[0-9]%', c) = 0
then 0
else patindex('%[0-9]%', c) - 1
end
);
Output:
+------+
| c |
+------+
| AD |
| AH |
| AK1 |
| B1 |
| B2 |
| B3x |
| B3y |
| B11 |
| C1 |
| C6 |
| C12x |
| UIP |
+------+