Sort the Alphanumeric Column in SQL

Anuja Godse

6y3.2kSQL

I want sort the below Alphanumeric Column in SQL Server.

Column A

SP-SS-2
SP-FAB-8
WD-1
WD-4A
WD-11
WD-10
WD-2
WD-20

I want sorted by ascending order Output is as follows:

Column A

SP-FAB-8
SP-SS-2
WD-1
WD-2
WD-4A
WD-10
WD-11
WD-20

Any tricks to make it sort properly?

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Malvik Bhavsar6y ago
Hey Anuja,

Try like this.

DECLARE @temp TABLE
(
string NVARCHAR(50)
)
INSERT INTO @temp (string)
VALUES
('SP-SS-2'),
('SP-FAB-8'),
('WD-1'),
('WD-4A'),
('WD-11'),
('WD-10'),
('WD-2'),
('WD-20')
select subsrt1 as Value from (
SELECT subsrt1,LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) as num,
SUBSTRING(SUBSTRING(LEFT(subsrt1, LEN(subsrt1) - PATINDEX ('%[^0-9][0-9]%', REVERSE(subsrt1))),
CHARINDEX('-', subsrt1) + 1, 100), CHARINDEX('-', subsrt1) + 1, 100) AS Numeric2,
-9999+ROW_NUMBER() OVER (ORDER BY subsrt1) as R ,
case when SUBSTRING(SUBSTRING(LEFT(subsrt1, LEN(subsrt1) - PATINDEX ('%[^0-9][0-9]%', REVERSE(subsrt1))),
CHARINDEX('-', subsrt1) + 1, 100), CHARINDEX('-', subsrt1) + 1, 100) = '' then LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) else -9999+ROW_NUMBER() OVER (ORDER BY subsrt1)
end as nn,
case when PATINDEX('%[^a-z]%',subsrt1) > 0 then Stuff(replace(subsrt1,'-',''), PatIndex('%[^a-z]%', replace(subsrt1,'-','')), 1, '') else '' end as al
FROM (
SELECT subsrt1= string,subsrt = SUBSTRING(string, pos, LEN(string))
FROM (
SELECT string, pos = PATINDEX('%[0-9]%', string)
FROM @temp
) d
) t) m
order by cast (nn as int)
Anuja Godse6y ago
it throws an error:
Conversion failed when converting the nvarchar value '4A' to data type int
Mageshwaran R6y ago
Hi,
Try this query:
SELECT column_A
FROM table_name
ORDER BY LEFT(column_A,PATINDEX('%[0-9]%',column_A)-1)
CONVERT(INT,SUBSTRING(Column_A,PATINDEX('%[0-9]%',Column_A),LEN(Column_A)))
Laxmidhar Sahoo6y ago
I send a link look it

https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly
Anuja Godse6y ago
it throws an error:
Type signed is not a defined system type.
Laxmidhar Sahoo6y ago
hi

try with below syntax

SELECT tbl_column, CAST(tbl_column as SIGNED) AS casted_column FROM db_table ORDER BY casted_column ASC , tbl_column ASC