3

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.

Musab M. Jafri
  • 246
  • 1
  • 4
  • 13
  • 3
    This is almost always a sign of normalization misses. When you have to resort to string manipulation in the order by it is usually because the data contains two (or more) values in a single tuple. This violates 1NF and causes all sort of anguish. – Sean Lange Feb 19 '18 at 15:25
  • Out of interest, could you also, have data that perhaps looks like `'B3y2'` and `'B3y11'`? If so, which should be ordered first? *Edit: Also, if the answer is yes, how many levels could you therefore get to? Could you end up with a record with the value `'B3y11A3d12AS4'`?* – Thom A Feb 19 '18 at 15:30
  • @SeanLange yes I agree to that, neither the database nor the data is in my control here. The requirement was presented such that all columns returned by a particular stored procedure should be sort-able and in case of this particular column they were not happy with the default way a string gets sorted. – Musab M. Jafri Feb 20 '18 at 04:57
  • @Larnu it doesn't seem at the moment that the data may have that structure yet, but I may have take that into consideration i guess. – Musab M. Jafri Feb 20 '18 at 04:58

1 Answers1

2

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  |
+------+
Musab M. Jafri
  • 246
  • 1
  • 4
  • 13
iamdave
  • 12,023
  • 3
  • 24
  • 53
  • This is almost about it for the given string formats, although there are two problems, 1 being the chunk has a -1 in the end that will most likely result in an error, and 2 being minor issue with having 'UIP' come as 3rd instead of the last item in result. But this is almost the answer required. I will make the edit in your answer to work with this. – Musab M. Jafri Feb 20 '18 at 07:11
  • @MusabM.Jafri This is important information that should have been included in your question. You will note how my results match the required output in your question. – iamdave Feb 20 '18 at 09:52
  • I copied and executed your script brother and it didn't result in UIP being at the right spot, that is what I commented. Along with the -1 at the end of your code also giving an error. Both points have no related to the question it self. – Musab M. Jafri Feb 20 '18 at 12:46