اگر بازم داده هایتون می تونه پیچیده تر از این حرفا باشه دو روش زیر هم می تونید امتحان کنید:
declare @t table
(a varchar(10),
b varchar(10),
c varchar(10),
d varchar(10),
e varchar(10));
insert @t
values ('1','1','1','1','1'),
('1','1','1','2/1000','2'),
('1','1','1','1','3'),
('1','1','1','1/1','1'),
('1','1','1','99/0','1'),
('1','1','1','9','1');
--msalim 01
SELECT a, b, c ,d ,e
FROM (SELECT CASE WHEN CHARINDEX('/', a) = 0 THEN a + '/0' ELSE a END AS a1,
CASE WHEN CHARINDEX('/', b) = 0 THEN b + '/0' ELSE b END AS b1,
CASE WHEN CHARINDEX('/', c) = 0 THEN c + '/0' ELSE c END AS c1,
CASE WHEN CHARINDEX('/', d) = 0 THEN d + '/0' ELSE d END AS d1,
CASE WHEN CHARINDEX('/', e) = 0 THEN e + '/0' ELSE e END AS e1, *
FROM @t) AS D
ORDER BY LEN(LEFT(a1, CHARINDEX('/', a1))), LEFT(a1, CHARINDEX('/', a1)),
LEN(RIGHT(a1, LEN(a1) - CHARINDEX('/', a1))), RIGHT(a1, LEN(a1) - CHARINDEX('/', a1)),
LEN(LEFT(b1, CHARINDEX('/', b1))), LEFT(b1, CHARINDEX('/', b1)),
LEN(RIGHT(b1, LEN(b1) - CHARINDEX('/', b1))), RIGHT(b1, LEN(b1) - CHARINDEX('/', b1)),
LEN(LEFT(c1, CHARINDEX('/', c1))), LEFT(c1, CHARINDEX('/', c1)),
LEN(RIGHT(c1, LEN(c1) - CHARINDEX('/', c1))), RIGHT(c1, LEN(c1) - CHARINDEX('/', c1)),
LEN(LEFT(d1, CHARINDEX('/', d1))), LEFT(d1, CHARINDEX('/', d1)),
LEN(RIGHT(d1, LEN(d1) - CHARINDEX('/', d1))), RIGHT(d1, LEN(d1) - CHARINDEX('/', d1)),
LEN(LEFT(e1, CHARINDEX('/', e1))), LEFT(e1, CHARINDEX('/', e1)),
LEN(RIGHT(e1, LEN(e1) - CHARINDEX('/', e1))), RIGHT(e1, LEN(e1) - CHARINDEX('/', e1));
--msalim 02
SELECT a, b, c ,d ,e
FROM (SELECT CASE WHEN CHARINDEX('/', a) = 0 THEN a + '/0' ELSE a END AS a1,
CASE WHEN CHARINDEX('/', b) = 0 THEN b + '/0' ELSE b END AS b1,
CASE WHEN CHARINDEX('/', c) = 0 THEN c + '/0' ELSE c END AS c1,
CASE WHEN CHARINDEX('/', d) = 0 THEN d + '/0' ELSE d END AS d1,
CASE WHEN CHARINDEX('/', e) = 0 THEN e + '/0' ELSE e END AS e1, *
FROM @t) AS D
ORDER BY LEFT(a1, CHARINDEX('/', a1) - 1) * 1, RIGHT(a1, LEN(a1) - CHARINDEX('/', a1)) * 1,
LEFT(b1, CHARINDEX('/', b1) - 1) * 1, RIGHT(b1, LEN(b1) - CHARINDEX('/', b1)) * 1,
LEFT(c1, CHARINDEX('/', c1) - 1) * 1, RIGHT(c1, LEN(c1) - CHARINDEX('/', c1)) * 1,
LEFT(d1, CHARINDEX('/', d1) - 1) * 1, RIGHT(d1, LEN(d1) - CHARINDEX('/', d1)) * 1,
LEFT(e1, CHARINDEX('/', e1) - 1) * 1, RIGHT(e1, LEN(e1) - CHARINDEX('/', e1)) * 1;
/*
a b c d e
---------- ---------- ---------- ---------- ----------
1 1 1 1 1
1 1 1 1 3
1 1 1 1/1 1
1 1 1 2/1000 2
1 1 1 9 1
1 1 1 99/0 1
*/