Custom AlphaNumeric Sorting in SQL Server

Refer the Below thest query for your referance.

SQL:

DECLARE @TableTester TABLE (TESTER VARCHAR(50)) 
 -- TO STORE VALUE IN TEMP TABLE AFTER SPLIT THE ; VALUES AND SORT BY FIRST VALUE
DECLARE @TableTester1 TABLE (TESTER VARCHAR(50),TESTER2 VARCHAR(50))
-- LAST TABLE WHERE SORTED OUTPUT WILL STORE
DECLARE @TableTester2 TABLE (TESTER VARCHAR(50)) 

INSERT @TableTester
VALUES ('1'),('2'),('3'),('4'),('10'),('20'),('5 ; 6'),('122'),('256 ; 78'),('U1 ; U2'),('U3 ; 7'),('C1'),('U4'),('WC23'),('WC214 ; U4'),('U10')

-- INSERT IN TO SECOND TEMP TABLE TO STORE VALUES BY TESTER AND SORT VALUES
INSERT @TableTester1
SELECT Tester
	  ,CASE WHEN PATINDEX('%[;]%',TESTER) > 0 THEN
       RTRIM(LTRIM(LEFT(TESTER,PATINDEX('%[;]%',TESTER)-1)))
	   ELSE TESTER END SortValue
FROM @TableTester
ORDER BY CASE WHEN PATINDEX('%[0-9]%',TESTER) > 0 THEN 1
              ELSE 2 END -- TO SORT PATTERN BY ALPHANUMERIC AND THEN BY ALPHABETS
         , CASE WHEN PATINDEX('%[0-9]%',TESTER) > 1 THEN
            RTRIM(LTRIM(LEFT(TESTER,PATINDEX('%[0-9]%',TESTER)-1)))
           END -- ALPHANUMERIC SORTING ON SORTED OUTPUT BY ALPHABETS BEFORE NUMBERS


-- INSERT IN TO THIRD TABLE FIRST TIME ONLY NUMERIC VALUES BY ASC ORDER
INSERT INTO @TableTester2
SELECT TESTER
FROM @TableTester1
WHERE PATINDEX('%[A-Z]%',TESTER2) = 0 
ORDER BY CAST(TESTER2 AS FLOAT)

-- INSERT IN TO THIRD TABLE SECOND TIME ONLY FOR ALPHANUMERIC VALUES BY SORTING
INSERT INTO @TableTester2
SELECT TESTER
FROM @TableTester1
WHERE PATINDEX('%[A-Z]%',TESTER2) > 0
ORDER BY CASE WHEN PATINDEX('%[0-9]%',TESTER2) > 1 THEN
            LEFT(TESTER2,PATINDEX('%[0-9]%',TESTER2)-1) ELSE TESTER2
           END -- ALPHANUMERIC SORTING ON SORTED OUTPUT BY ALPHABETS BEFORE NUMBERS
         ,CASE WHEN PATINDEX('%[0-9]%',TESTER2) > 1 THEN
           CAST(SUBSTRING(TESTER2,PATINDEX('%[0-9]%',TESTER2),LEN(TESTER2)) as FLOAT)            
          END -- ALPHANUMERIC SORTING ON SORTED OUTPUT BY NUMBERS AFTER ALPHABETS

-- SORTED OUTPUT FROM LAST TABLE
SELECT * FROM @TableTester2

Output:

Hi kalpesh,

I also got the correct answer. 

DECLARE @TableTester TABLE (TESTER VARCHAR(50));

INSERT @TableTester VALUES
('1'),
('2'),
('3'),
('4'),
('10'),
('20'),
('5 ; 6'),
('122'),
('256 ; 78'),
('U1 ; U2'),
('U3 ; 7'),
('C1'),
('U4'),
('WC23'),
('WC214 ; U4'),
('U10');

SELECT Tester FROM @TableTester
ORDER BY 
    CASE WHEN TESTER LIKE '[A-Z]%' THEN LEFT(TESTER,PATINDEX('%[0-9]%',TESTER)-1) ELSE NULL END,
    CAST(
        CASE WHEN TESTER LIKE '%[0-9]%' 
                THEN REPLACE(SUBSTRING(TESTER,PATINDEX('%[0-9]%',TESTER),CHARINDEX(' ;',TESTER+' ;')-1),' ;','') 
                ELSE TESTER END
                        as int)