Sort (Order By) VARCHAR column contains Number in SQL Server

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

Hi dharmendr,

I also got the answer before I read your reply.

This is my Answer:

Your Answer:

Thank you so much for the effort ! =)