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?
-
do substring of the number part and use in orderZaynul Abadin Tuhin– Zaynul Abadin Tuhin2019-06-12 06:37:35 +00:00Commented Jun 12, 2019 at 6:37
-
You need to extract the integer part.jarlh– jarlh2019-06-12 06:37:42 +00:00Commented Jun 12, 2019 at 6:37
-
how can I do that?Sora– Sora2019-06-12 06:39:31 +00:00Commented Jun 12, 2019 at 6:39
-
What should happen if there's no number inside the parenthesis? Or if the there are two records with the same number but different text?Zohar Peled– Zohar Peled2019-06-12 07:44:51 +00:00Commented Jun 12, 2019 at 7:44
Add a comment
|
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.
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)
Comments
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)
