Sorting column that contains string with numeric value

You must have to concern data sorting when you work with wide range of data source. Well nothing to say more about shorting which is very important. Okay let’s consider the following scenario:
You have a table with few columns & you will sort the table based on a specific column that contains numeric value with string type value. A sample table is given below:

Table# tempTable

c1 c2

1 368 Mirpur
2 243 Gulshan
3 101 DOHS
. …………..
etc.,

So now, you have to sort the table base on c2, how can you do that? It’s very simple, to achieve this we will use PATINDEX() function. The following query will resolve the issue.

Example:

DECLARE @tempTable TABLE (id INT, String_WithN_umericValue VARCHAR(100))
INSERT INTO @tempTable VALUES (1,’7th’)
INSERT INTO @tempTable VALUES(2,’4th’)
INSERT INTO @tempTable VALUES(3,’9th’)
INSERT INTO @tempTable VALUES(4,’10th’)
INSERT INTO @tempTable VALUES(5,’2nd’)
INSERT INTO @tempTable VALUES(6,’5th’)
INSERT INTO @tempTable VALUES(7,’8th’)
INSERT INTO @tempTable VALUES(8,’1st’)
INSERT INTO @tempTable VALUES(9,’3rd’)
INSERT INTO @tempTable VALUES(10,’6th’)

SELECT * FROM @tempTable ORDER BY CAST(LEFT(String_WithN_umericValue,PATINDEX(‘%[^0-9]%’,String_WithN_umericValue)-1) AS INT)

More information about PATINDEX() function can be found at this link.