When I was writing this blog post I had a hard time to come up with the title of the blog post so I did my best to come up with one. Here is the reason why? I wrote a blog post earlier SQL SERVER – Find First Non-Numeric Character from String. One of the questions was that how that blog can be useful in real life scenario. This blog post is the answer to that question. Let us first see a problem.
We have a table which has a column containing alphanumeric data. The data always has first as an integer and later part as a string. The business need is to order the data based on the first part of the alphanumeric data which is an integer. Now the problem is that no matter how we use ORDER BY the result is not produced as expected. Let us understand this with example.
Prepare a sample data:
-- How to find first non numberic character USE tempdb GO CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100)) GO INSERT INTO MyTable (ID, Col1) SELECT 1, '1one' UNION ALL SELECT 2, '11eleven' UNION ALL SELECT 3, '2two' UNION ALL SELECT 4, '22twentytwo' UNION ALL SELECT 5, '111oneeleven' GO -- Select Data SELECT * FROM MyTable GO
The above query will give following result set.
Now let us use ORDER BY COL1 and observe the result along with Original SELECT.
-- Select Data SELECT * FROM MyTable GO -- Select Data SELECT * FROM MyTable ORDER BY Col1 GO
The result of the table is not as per expected.
We need the result in following format.
Here is the good example of how we can use PATINDEX.
-- Use of PATINDEX SELECT ID, LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1) 'Numeric Character', Col1 'Original Character' FROM MyTable ORDER BY LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1) GO
We can use PATINDEX to identify the length of the digit part in the alphanumeric string (Remember: Our string has a first part as an int always. It will not work in any other scenario). Now you can use the LEFT function to extract the INT portion from the alphanumeric string and order the data according to it.
You can easily clean up the script by dropping following table.
DROP TABLE MyTable GO
Here is the complete script so you can easily refer it.
-- How to find first non numberic character USE tempdb GO CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100)) GO INSERT INTO MyTable (ID, Col1) SELECT 1, '1one' UNION ALL SELECT 2, '11eleven' UNION ALL SELECT 3, '2two' UNION ALL SELECT 4, '22twentytwo' UNION ALL SELECT 5, '111oneeleven' GO -- Select Data SELECT * FROM MyTable GO -- Select Data SELECT * FROM MyTable ORDER BY Col1 GO -- Use of PATINDEX SELECT ID, Col1 'Original Character' FROM MyTable ORDER BY LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1) GO DROP TABLE MyTable GO
Well, isn’t it an interesting solution. Any suggestion for better solution? Additionally any suggestion for changing the title of this blog post?
Reference : Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
instead of substring and order it in smaller piece, how about concatenate it and order in bigger one?
consider:
select * from mytable order by right(‘00000’ + mycol,10)
Superb article. Solution to real time scenario.
Alternate solution:
SELECT ID, Col1 ‘Original Character’ FROM MyTable ORDER BY UNICODE(Col1), PATINDEX(‘%[^0-9]%’, Col1), Col1
Super Nice Harsh!
Thanks,
Both solutions will work if string has first part as an int and later part as string.
Minor addition in these scripts can handle strings only has int part(no alphabet characters).
So, One of two limitations will be removed
SELECT ID,
Col1 ‘Original Character’
FROM MyTable
ORDER BY LEFT(Col1,PATINDEX(‘%[^0-9]%’,Col1+’a’)-1)
SELECT ID, Col1 ‘Original Character’ FROM MyTable ORDER BY UNICODE(Col1), PATINDEX(‘%[^0-9]%’, Col1+’a’), Col1
Hi,
I also have an alternate solution for your “challenge”:
select ID, Col1 as Original_String, SUBSTRING(Col1, 0, PATINDEX(‘%[a-z]%’, Col1)) as Numeric_Character
from MyTable
order by Numeric_Character
I think it is quite elegant…. SUBSTRING(Col1, 0, PATINDEX(‘%[a-z]%’, Col1))… SUBSTRING returns character data if expr (Col1) is character type (our case here) so the result is ok…
You are doing a great job here!
Thanks,
Eduard U.
how to use this function if the number is between the string
Ex: NSE-USD-3Y-SB-OP
….
Order by Numeric Values formatted as string, then the correct order shouldn’t be like this?
SELECT ID,
Col1 ‘Original Character’
FROM MyTable
ORDER BY CAST(LEFT(Col1,PATINDEX(‘%[^0-9]%’,Col1)-1) AS INT)
GO
Very Good.
Very Good
If you want result in numeric order then you can use this code,
SELECT ID,
LEFT(Col1,PATINDEX(‘%[^0-9]%’,Col1)-1) ‘Numeric Character’,
Col1 ‘Original Character’
FROM MyTable
ORDER BY cast(LEFT(Col1,PATINDEX(‘%[^0-9]%’,Col1)-1) as int)
GO
ID Numeric Character Original Character
1 1 1one
3 2 2two
2 11 11eleven
4 22 22twentytwo
5 111 111oneeleven