I need quick help in sorting data at SQL side. I am using Sqlserver 2012
(good if answer give with the new feature).
I already search some links as Sorting in alphanumeric , Alphanumeric string Sorting in Sqlserver - Code project. But does not give the desired result.
Still What I have try :
CREATE TABLE dbo.Section
(
Section varchar(50) NULL
)
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsit no.43')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsit no.41')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 11')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 1')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 12')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 2')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 3')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 4')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 40')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 41')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite no.20')
INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite no.41')
INSERT INTO dbo.Section (Section.Section) VALUES ('Cabin')
INSERT INTO dbo.Section (Section.Section) VALUES ('Group Tent Campsite')
INSERT INTO dbo.Section (Section.Section) VALUES ('Tent Campsite')
INSERT INTO dbo.Section (Section.Section) VALUES ('test1')
INSERT INTO dbo.Section (Section.Section) VALUES ('test2')
INSERT INTO dbo.Section (Section.Section) VALUES ('test11')
SELECT Section
FROM dbo.Section
--Show normal Sort
SELECT Section
FROM dbo.Section
ORDER BY Section
--Show AlphaNumberic Sort
SELECT Section
FROM dbo.Section
ORDER BY LEFT(Section,PATINDEX('%[0-9]%',Section)), -- alphabetical sort
CONVERT(varchar(50),SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section))) -- numerical sort
--cleanup our work
--DROP Table dbo.Section
Now what I want is : if same string find in alphabet part sort on that first and then numeric (consider space also if possible or you may give the result without space like Campsite no.41 and Campsite No. 41 will give in same order)
Actual Result Expected Result
Campsit no.41 Campsit no.41
Campsit no.43 Campsit no.43
Campsite No. 1 Campsite No. 1
Campsite No. 11 Campsite No. 2
Campsite No. 12 Campsite No. 3
Campsite No. 2 Campsite No. 4
Campsite No. 21 Campsite No. 11
Campsite No. 3 Campsite No. 12
Campsite No. 4 Campsite No. 21
Campsite No. 40 Campsite No. 40
Campsite No. 41 Campsite No. 41
Campsite no.20 Campsite no.20 --this will good to come here, if possible or if not, then remove space and set approriate
Campsite no.41 Campsite no.41 --this will good to come here, if possible or if not, then remove space and set approriate
Group Tent Campsite Group Tent Campsite
Tent Campsite Tent Campsite
test1 test1
test11 test2
test2 test11
See Question&Answers more detail:os