CODEWITHSUNDEEP
sql-server
mirku
mirku

Reputation: 79

SQL Server SELECT query ordering by substring

I have a column in a SQL Server table that has the following rows:

MyColumn : C1_xxx1,C2_xxx1,C3_xxx1,C1_xxx2,C1_xxx3,C3_xxx2 etc

It is a text column that contains strings that have the following format: CY_mystring where Y is a number from 1 to 5, followed by the '_' character then mystring that can have any value.

Is there a way to make a select return this column ordered as following:

C1_xxx1
C1_xxx2
C1_xxx3
......
C1_xxxn
C2_xxx1
......
C2_xxxn
C3_xxx1
.......
C3_xxxn

etc

Ordered by the CY_ substring.

thank you

Upvotes: 1

Views: 4058

Answers (2)

Andomar
Andomar

Reputation: 238076

You say that Y is a number from 1 to 5 it's always one character long. Assuming the format is xY_xxxZ, you can order on Y then Z like:

order by 
        substring(MyColumn,2,1) -- Second digit
,       right(MyColumn,1) -- Last digit

If Z can be longer than one character (i.e. 10 or higher) you can use pathindex to determine the number of digits at the end:

order by
        substring(MyColumn,2,1) -- Second digit
,       right(MyColumn, patindex('%[^0-9]%', reverse(MyColumn))-1) -- Digits at end

Upvotes: 0

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195982

This should do it .. (order first by the first two chars, and then by the last char (assuming that the final n is always one digit long))

SELECT
   Column1
FROM
   TABLENAME
ORDER BY
   LEFT(Column1,2) ASC, 
   RIGHT(Column1,1) ASC

Upvotes: 5

Related Questions