Skip to content
Advertisement

SQL query to sort data while insert , first numbers then alphabets an last symbols

I am getting trouble to write SQL sort Query, I have table as follows

enter image description here

And I want to sort above data as, First should number and then alphabets and last special symbols like following table.

First numbers should sort like 1,2,3,11,22,33 then Alphabets should sort like a ,b,c,..z and then symbols, like following table

enter image description here

I have tried many ways but still not getting correct way, please help me to write query.

Advertisement

Answer

You can use a CASE WHEN on the ORDER BY to create some groups:

SELECT *
FROM table_name
ORDER BY
  CASE WHEN LEFT(FilterParameterValue, 1) LIKE '[0-9]' OR LEFT(FilterParameterValue, 2) LIKE '-[0-9]' OR LEFT(FilterParameterValue, 2) LIKE '+[0-9]' THEN 1 ELSE 0 END DESC, -- group for numbers
  CASE WHEN ISNUMERIC(FilterParameterValue) = 1 THEN CAST(FilterParameterValue AS MONEY) ELSE NULL END ASC, -- order the numeric values
  CASE WHEN LEFT(FilterParameterValue, 1) LIKE '[A-Za-z]' THEN 1 ELSE 0 END DESC, -- group for chars from A to Z (capital / non capital)
  colName

demo on dbfiddle.uk

9 People found this is helpful
Advertisement