You must Sign In to post a response.
• # Sort Alphanumeric Column

How to Sort a Column that Contains Character-Separated Numbers in sql?
example- Input...
1-1-1,
1-10,
2-1,
2,
2-1-3,
1-1.
Result Want to:
1-1,
1-1-1,
1-10,
2,
2-1,
2-1-3
• Hi,
Use below script:
USE [demo]
GO
CREATE TABLE [dbo].[Table_1](
[password] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table_1] ([password]) VALUES (N'1-1-1')
GO
INSERT [dbo].[Table_1] ([password]) VALUES (N'1-10')
GO
INSERT [dbo].[Table_1] ([password]) VALUES (N'1-1')
GO
INSERT [dbo].[Table_1] ([password]) VALUES (N'2')
GO
INSERT [dbo].[Table_1] ([password]) VALUES (N'2-1-3')
GO
INSERT [dbo].[Table_1] ([password]) VALUES (N'2-1')
GO
SELECT password FROM dbo.Table_1 ORDER BY
CASE WHEN PATINDEX('%[0-9]%',password) > 1 THEN
LEFT(password,PATINDEX('%[0-9]%',password)-1)
ELSE
password
END ,
CASE WHEN PATINDEX('%[0-9]%',password) > 1 THEN
CAST(SUBSTRING(password,PATINDEX('%[0-9]%',password),LEN(password)) as float)
ELSE
NULL
END
GO

• There is no direct way to do this. You can either sort by numeric or varchar. But you have to use some tricks to do that. Following is one of the trick that I am using in our application. You can try this, it will work for your requirement also.
`Select CName from(SELECT CName, CAST(replace([CName],'-','') AS INT) as CValue FROM YourTableName ) AS AA order by AA.CValue`
The above is simple logic. Just replace the '-" into blank then converting that into numeric then sorting. I am handling it as sub query. Try it.

By Nathan
Direction is important than speed

• Hai Ankush,
You can smple replace the '-' with empty and then use Order By clause which will make the string as per your requirements.
Hope it will be helpful to you.

Regards,
Pawan Awasthi(DNS MVM)
+91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
pawansoftit@gmail.com

• hello Shshi sir,
If I add another value...i.e.
INSERT [dbo].[Table_1] ([password]) VALUES (N'1-12-12')
GO
INSERT [dbo].[Table_1] ([password]) VALUES (N'1-12-2')
GO

output get wrong...

• Few more examples in below links,

http://social.technet.microsoft.com/wiki/contents/articles/30527.t-sql-how-to-sort-a-column-that-contains-character-separated-numbers.aspx

http://www.codeproject.com/Articles/842541/How-Do-I-Use-SQL-Server-to-Sort-Alphanumeric-Value

Regards,
Asheej T K

• Sign In to post your comments