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
You must Sign In to post a response.
Category: Windows 8
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
#767418
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
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
#767419
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.
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
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
#767420
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
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
#767427
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...
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...
#767430
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
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
Return to Return to Discussion Forum