ORDER BY for complex alphanumeric string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ORDER BY for complex alphanumeric string

Author

Topic

19mary333
Starting Member

5 Posts

Posted - 2014-05-08 : 07:13:55

I have a table PRODOTTI with ID, CODICE (NVARCHAR(100))
Query with order by:
SELECT CODICE FROM PRODOTTI ORDER BY CODICE

I want this order of strings:

A
A
A.client1cc
A.c1pr
A.10
A.10.client1df
A.10.f2se
A.10.f2se
A.10.10
A.10.10.01
A.10.10.01
A.10.11
A.10.11
A.10.11.01
A.10.11.01
A.10.11.02
A.10.11.02
A.11
A.11
A.11.12.21
A.12.02
B
B
01
02
03
03.005
03.010
03.015
04
05
06

but I obtain:

01
02
03
03.005
03.010
03.015
04
05
06
A
A
A.10
A.10.10
A.10.10.01
A.10.10.01
A.10.11
A.10.11
A.10.11.01
A.10.11.01
A.10.11.02
A.10.11.02
A.10.client1df
A.10.f2se
A.10.f2se
A.11
A.11
A.11.12.21
A.12.02
A.c1pr
A.client1cc
B
B

According to SQL standards, I have first a numerical order and then alphabetical. I want alphabetically sort, how???. HELP me!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-08 : 07:55:43

maybe this:
order by case when left(codice,1) like '[^0-9]' then '0000000000'+codice else codice end

Too old to Rock'n'Roll too young to die.

19mary333
Starting Member

5 Posts

Posted - 2014-05-08 : 08:19:44

mmm your solution resolves in part the problem. I have results like this:

A
A
A.10
A.10
A.10.11.02
A.10.12
A.10.12
A.10.12.01
A.10.client1df
A.10.f2se
A.10.f2se
A.11
A.11
A.11.10
A.11.10
A.11.10.01
A.11.10.01
A.11.10.80
A.11.10.80
A.11.10.90
A.11.10.90
A.11.11
A.11.12
A.11.12.01
A.11.12.01
A.c1pr
A.client1cc
B
B
01
02
03
03.005
03.010
03.015
04
05
06

When there are only numeric or only alphabetic values the sort is correct. But, as you see, it first select A.11.12, A.11.12.01...and then strings like A.c1pr, A.client1cc. So the problem still remains for mixed string.

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-08 : 08:57:39

Have you tried something like this...

ORDER BY CASE WHEN PATINDEX('[A-Z]%',CODICE)<>0 THEN 1 ELSE 2 END

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-05-08 : 09:28:41

As ASCII orders numbers before letters but EBCDIC orders numbers after letters, the simple solution is just to cast to an EBCDIC collation.
The casting will be quite expensive but it should work. eg:


SELECT CODICE
FROM PRODOTTI
ORDER BY CODICE COLLATE SQL_EBCDIC037_CP1_CS_AS;

19mary333
Starting Member

5 Posts

Posted - 2014-05-08 : 09:33:36

MuralikrishnaVeera thanks for your support! The results with your solution are far from the sort auspicated. I have result like this:

B.10
B.11
B.13
B.13.10
B.13.11
B.13.11.01
B.13.11.20
B.13.10.01
B.13.10.02
B.11.10
B.11.10.01
B.11.10.02
B.10.10
B.10.10.01
B.10.10.02
B.12.10
A.10
A.11
A.12
A.12.01
A.12.02
A.11.10
A.11.11
A.11.12
A.11.15
A.11.15.01
A.11.15.02
A.11.12.01
A.11.12.21
A.11.10.01
A.11.10.02
A.11.10.07
A.10.10
A.10.11
A.10.12
A.10.12.01
A.10.12.02
A.10.11.01
A.10.11.02
A.10.10.01
A.10.10.02
nn.01
R.82
R.82.10
C.30
C.30.10
C.30.10.10
B.12
B.10
B.11
A.10
A.11
A.12
A.c1pr
A.client1cc
A.12.01
A.12.02
A.11.10
A.11.11
A.11.12
A.11.15
A.11.15.01
A.11.15.02
A.11.12.01
A.11.12.20
A.11.12.21
A.11.10.01
A.11.10.02
A.11.10.07
A.11.10.80
A.11.10.90
A.10.10
A.10.11
A.10.12
A.10.client1df
A.10.f2se
A.10.f2se
A.10.12.01
A.10.12.02
A.10.11.01
A.10.11.02
A.10.10.01
A.10.10.02
A
B
A
02
01
03
04
05

19mary333
Starting Member

5 Posts

Posted - 2014-05-08 : 09:41:11

IFOR, I have results like this:

01
02
03
03.005
A
A
A.10
A.10
A.10.10
A.10.10
A.10.10.01
A.10.10.01
A.10.10.02
A.10.10.02
A.10.11
A.10.11
A.10.11.01
A.10.11.01
A.10.11.02
A.10.11.02
A.10.12
A.10.12
A.10.12.01
A.10.12.01
A.10.12.02
A.10.12.02
A.10.client1df
A.10.f2se
A.10.f2se
A.11
A.11
A.11.10
A.11.10
A.11.10.01
A.11.10.01
A.11.10.02
A.11.10.02
A.11.10.07
A.11.10.90
A.11.10.90
A.11.11
A.11.15
A.11.15
A.11.15.01
A.12
A.12
A.12.01
A.12.01
A.12.02
A.12.02
A.c1pr
A.client1cc
B
B
B.10
B.10

Before A.10, we should have string like (A.c1pr, A.client1cc). This sort makes me mad!!! @.@

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-05-08 : 09:50:58

Umm.... The following works for me:


-- *** Test Data ***
CREATE TABLE #t
(
	Test varchar(30) NOT NULL
);
INSERT INTO #t
VALUES ('01'),('02'),('03'),('03.005'),('A'),('A'),('A.10'),('A.10'),('A.10.10'),('A.10.10'),('A.10.10.01'),('A.10.10.01'),('A.10.10.02')
,('A.10.10.02'),('A.10.11'),('A.10.11'),('A.10.11.01'),('A.10.11.01'),('A.10.11.02'),('A.10.11.02'),('A.10.12'),('A.10.12'),('A.10.12.01')
,('A.10.12.01'),('A.10.12.02'),('A.10.12.02'),('A.10.client1df'),('A.10.f2se'),('A.10.f2se');
-- *** End Test Data ***

SELECT Test
FROM #t
ORDER BY Test COLLATE SQL_EBCDIC037_CP1_CS_AS;

19mary333
Starting Member

5 Posts

Posted - 2014-05-08 : 10:29:06

IFOR, I have tested this method with a new simple table. Your code works!!!! it's a problem of my complex query:
I have a recursive one. The structure is like this

WITH Parents AS
(
SELECT MenuItemId, URL, ParentItemId, ItemOrder
FROM CambsMenu

UNION ALL

SELECT si.MenuItemId, si.URL, si.ParentItemId, si.ItemOrder
FROM CambsMenu si INNER JOIN Parents p
ON si.ParentItemId = p.MenuItemId
)

SELECT DISTINCT *
FROM Parents ORDER BY (column) COLLATE SQL_EBCDIC037_CP1_CS_AS;

For this kind of query sort not work???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources