Ordering a string containing an integer inside SQL

Question

I have the below content inside a table in SQL

I tried to order by title but I didn't work, is there any SQL statement to use so I can order by the number inside InterSciences Competition?

Answer 1

You can try like following query.

;WITH cte 
     AS (SELECT title, 
                Cast(Substring(title, Charindex('(', title) + 1, 
                     Charindex(')', title) - 
                     Charindex('(', title) - 1) 
                     AS INT) AS OC 
         FROM   yourtable) 
SELECT * 
FROM   cte 
ORDER  BY oc 

In above query, the number between brackets is extracted and converted to INT for ordering.

Online Demo