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: