Sort a varchar type column (Alphanumeric Values)

In my table VARCHAR data type column called CartonNo, that contain either numbers or number with letter it depends on. I want it to be sorted numerically, this would be an acceptable ascending sort order.

• Issue No. 1

For example, contains like following data

1-2
1
3-5
6
1-2A
6-12
7-8
9-12D
9-12
17
9-12B
19
20-30
15-18

Now I want to order the data as below

1
1-2
1-2A
3-5
6
6-12
7-8
9-12
9-12B
9-12D
15-18
17
19
20-30

• Issue No. 2

the bellow value How to sort alphanumeric?

PT15-8-2

PT15-12-10

PT15-12-3

PT15-12-572-575

PT15-12-31

PT11-96

PT11-133-36

PT11-133

PT16-136

PT8-644

PT09-655

PT9-655

PT12-752

PT14-752

PT13-752

PT14-753

PT10-778

PT13-977-978

PT13-979-988

Can anyone please tell me how should I write the query?

i am assuming before - you would have integer value always

declare @temp table
(val nvarchar(99))
insert into @temp values ('1-2'  )
insert into @temp values ('3-5'  )
insert into @temp values ('1-2A')
insert into @temp values ('6-12')
insert into @temp values ('6-7')
insert into @temp values ('6-12D')
insert into @temp values ('20-30')
insert into @temp values ('15-18')
--insert into @temp values ('3')
--insert into @temp values ('53')

QUERY

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)

Exactly Ordered

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
,CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT),
RIGHT(val,LEN(val)-CHARINDEX('-',val))

after run second query the output would be

UPDATE AFTER COMMENT: sometime varchar val won't come (3-5) or (1-12A) instead of whole number 3 or 5... at the time the above answer getting an error... how to correct way to sort.

-- DECLARE TABLE
DECLARE @temp TABLE
(val nvarchar(99))

--INSERT INTO TABLE
INSERT INTO @temp VALUES 
('1-2'), ('1'), ('3-5'), ('6'), ('1-2A'), ('6-12'), ('7-8'), ('9-12D'), ('9-12'), ('17'), ('9-12B'), ('19'), ('20-30'), ('15-18')

-- QUERY
SELECT * FROM @temp
ORDER BY
CASE 
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT)
    ELSE val 
    END ,val

RESULT:

StackExchange Database Administrators Q#117379, answer score: 3