Hi ayakamacy,
Refer the below Query.
You can perform some tricks by converting to a numeric after you discover the location of the first non-numeric. Appending a random character at the end makes it treat all strings the same even if the original string did not contain an alphabetic.
SQL
DECLARE @TblR8Pure AS TABLE(WireID INT,PadNo VARCHAR(20),Pattern INT,Tester VARCHAR(20))
INSERT INTO @TblR8Pure VALUES(1,'3_29',158,'40')
INSERT INTO @TblR8Pure VALUES(2,'3_18',173,'41')
INSERT INTO @TblR8Pure VALUES(3,'3_33',155,'44')
INSERT INTO @TblR8Pure VALUES(4,'3_7',182,'45')
INSERT INTO @TblR8Pure VALUES(5,'3_19',169,'47')
INSERT INTO @TblR8Pure VALUES(6,'3_10',181,'48')
INSERT INTO @TblR8Pure VALUES(7,'3_2',189,'50')
INSERT INTO @TblR8Pure VALUES(8,'3_21',172,'51')
INSERT INTO @TblR8Pure VALUES(9,'3_16',175,'52')
INSERT INTO @TblR8Pure VALUES(10,'3_22',164,'54')
INSERT INTO @TblR8Pure VALUES(16,'3_19',169,'225;219')
INSERT INTO @TblR8Pure VALUES(19,'3_10',181,'227')
INSERT INTO @TblR8Pure VALUES(17,'3_2',189,'229;355;320')
INSERT INTO @TblR8Pure VALUES(18,'3_21',172,'234;362;318')
INSERT INTO @TblR8Pure VALUES(11,'3_28',157,'100')
INSERT INTO @TblR8Pure VALUES(12,'3_20',170,'46,128')
INSERT INTO @TblR8Pure VALUES(13,'3_14',177,'42,128,28')
INSERT INTO @TblR8Pure VALUES(14,'3_37',149,'49,56,129')
INSERT INTO @TblR8Pure VALUES(15,'3_34',152,'53,122,237')
SELECT * FROM @TblR8Pure ORDER BY CONVERT(INT, LEFT(Tester, PATINDEX('%[^0-9]%', Tester + 'z')-1))
Output
WireID |
PadNo |
Pattern |
Tester |
1 |
3_29 |
158 |
40 |
2 |
3_18 |
173 |
41 |
13 |
3_14 |
177 |
42,128,28 |
3 |
3_33 |
155 |
44 |
4 |
3_7 |
182 |
45 |
12 |
3_20 |
170 |
46,128 |
5 |
3_19 |
169 |
47 |
6 |
3_10 |
181 |
48 |
14 |
3_37 |
149 |
49,56,129 |
7 |
3_2 |
189 |
50 |
8 |
3_21 |
172 |
51 |
9 |
3_16 |
175 |
52 |
15 |
3_34 |
152 |
53,122,237 |
10 |
3_22 |
164 |
54 |
11 |
3_28 |
157 |
100 |
16 |
3_19 |
169 |
225;219 |
19 |
3_10 |
181 |
227 |
17 |
3_2 |
189 |
229;355;320 |
18 |
3_21 |
172 |
234;362;318 |