CODEWITHSUNDEEP
sqlsql-serveralphanumeric
Hard Tacos
Hard Tacos

Reputation: 370

SQL Sort by Alphanumerical Order

So I have looked a few different example on here, however none of them seem to be working for me.

Here is a sample of what I need sorted:

Alpha #1
Beta #3
Charlie #2
Alpha #2
Charlie #1
Beta #2
Beta #1
Alpha #10

And when sorted I would like

Alpha #1
Alpha #2
Alpha #10
Beta #1
Beta #2
Beta #3
Charlie #1
Charlie #2

However when I use the following query, Alpha #10 does not follow the desired rule.

ORDER BY 
Case When IsNumeric(LEFT(MachineName,1)) = 1 
     Then CASE When IsNumeric(MachineName) = 1 
               Then Right(Replicate('0',20) + MachineName + '0', 20)
               Else Right(Replicate('0',20) + MachineName, 20) 
          END
     When IsNumeric(LEFT(MachineName,1)) = 0 
     Then Left(MachineName + Replicate('',21), 20)
End

I get this instead:

Alpha #1
Alpha #10
Alpha #2
...

I am new to LEFT and RIGHT, so I might be doing something wrong, so any guidance would be greatly appreciated!

Upvotes: 0

Views: 61

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

DECLARE @t TABLE(v VARCHAR(100))

INSERT INTO @t VALUES
('Alpha #1'),
('Beta #3'),
('Charlie #2'),
('Alpha #2'),
('Charlie #1'),
('Beta #2'),
('Beta #1'),
('Alpha #10'),
('Alpha #')

SELECT * FROM @t
ORDER BY CASE WHEN PATINDEX('%[0-9]%', v) > 1 THEN SUBSTRING(v, 1, PATINDEX('%[0-9]%', v) - 1) END, 
         CASE WHEN PATINDEX('%[0-9]%', v) > 1 THEN CAST(SUBSTRING(v, PATINDEX('%[0-9]%', v), LEN(v)) AS INT) END

Output:

Alpha #
Alpha #1
Alpha #2
Alpha #10
Beta #1
Beta #2
Beta #3
Charlie #1
Charlie #2

Upvotes: 2

Related Questions