Perfect example of why you should use the correct datatypes. This solution may work but it will likely be VERY slow.

for the cases where they are just numbers you can righ justify with something like:order by ...,replicate(' ', 50-len(episodeno)) + episodeno asc

If there are not too many types of exceptions for non-numerics (like the two you mentioned) you may be able to use CASE statement. One WHEN per exception type:

(this code is untested)

order by ...,case -1when episodeno not like '%[^0-9]%' then replicate(' ', 50-len(episodeno)) + episodeno when episodeno like '%of%' then left(episodeno, charindex('of', episodeno)-1)when episodeno = 'Pilot' then 0end

for this solution you would also need to add the logic to "right justify" the other CASEs.

if you want sorting them based on their numeric value, you cant have them as varchar.In my opinion best thing would be to bring them as DATENAME(yy,[date]) + DATENAME(wk,[date]) and then do formatting at front end to add /