Ordering a string containing an integer inside SQL

user_1428318 0
Viewed 72

I have the below content inside a table in SQL

enter image description here

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?

tsql sql-server sql
3 Answers

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

user_2973220

Using SUBSTRING() and PATINDEX(), this ordering is possible:

DECLARE @TestTable TABLE (TestVal VARCHAR (20));
INSERT INTO @TestTable (TestVal) VALUES ('Test (1)'), ('Test (10)'), ('Test (2)');

SELECT TestVal 
FROM @TestTable 
ORDER BY CAST(SUBSTRING(TestVal, PATINDEX('%[0-9]%', TestVal), LEN(TestVal) - PATINDEX('%[0-9]%', TestVal)) AS INT),
         LEFT(TestVal, PATINDEX('%[0-9]%', TestVal) - 2)

Output:

TestVal
---------
Test (1)
Test (2)
Test (10)

Demo on db<>fiddle

user_24517260

You can also try this:

declare @t table (title varchar(50))

insert into @t values ('InterSciences Competition (1)')
insert into @t values ('InterSciences Competition (10)')
insert into @t values ('InterSciences Competition (2)')


select * from @t
order by cast(replace(substring(title,CHARINDEX('(',title)+1,CHARINDEX(')',title)),')','') as INT)
user_62601570
Related