Sort the Alphanumeric Column in SQL

Question

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?

Malvik Bhavsar · Answer

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 Godse · Answer

it throws an error: Conversion failed when converting the nvarchar value '4A' to data type int

Mageshwaran R · Answer

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 Sahoo · Answer

I send a link look it https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly

Anuja Godse · Answer

it throws an error: Type signed is not a defined system type.

Laxmidhar Sahoo · Answer

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