It is fun when you have to deal with simple problems and there are no out of the box solution. I am sure there are many cases when we needed the first non-numeric character from the string but there is no function available to identify that right away. Here is the quick script I wrote down using PATINDEX. The function PATINDEX exists for quite a long time in SQL Server but I hardly see it being used. Well, at least I use it and I am comfortable using it. Here is a simple script which I use when I have to identify first non-numeric character.
-- How to find first non numeric 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
-- Use of PATINDEX
SELECT PATINDEX('%[^0-9]%',Col1) 'Position of NonNumeric Character',
SUBSTRING(Col1,PATINDEX('%[^0-9]%',Col1),1) 'NonNumeric Character',
Col1 'Original Character'
FROM MyTable
GO
DROP TABLE MyTable
GO
Here is the resultset:
Where do I use in the real world – well there are lots of examples. In one of the future blog posts I will cover that as well. Meanwhile, do you have any better way to achieve the same. Do share it here. I will write a follow up blog post with due credit to you.
Reference :Â Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
One more option is :
SELECT PATINDEX(‘%[a-z]%’,Col1) ‘Position of NonNumeric Character’,
SUBSTRING(Col1,PATINDEX(‘%[a-z]%’,Col1),1) ‘NonNumeric Character’,
Col1 ‘Original Character’
FROM MyTable
SELECT PATINDEX(‘%[a-z,A-Z]%’,Col1) ‘Position of NonNumeric Character’,
PATINDEX(‘%[^0-9]%’,Col1) ‘Position of NonNumeric Character’,
SUBSTRING(Col1,PATINDEX(‘%[^0-9]%’,Col1),1) ‘NonNumeric Character’,
Col1 ‘Original Character’
FROM MyTable
These first 2 solutions of %[a-z] & %[a-z,A-Z]% will work in given example.
But, it will not work in case of any special character is there, because it is returning first alphabet character of string
Harsh, You are right
Dear Mr. Pinal,
I have problem of using PIVOT in query. IT gives error message to set database compatibility.
Waiting for your reply.
Charindex seems to do this a bit faster :
SELECT CHARINDEX ( ‘%[^0-9]%’,Col1 ) ‘Position of NonNumeric Character’,
SUBSTRING(Col1,CHARINDEX ( ‘%[^0-9]%’,Col1 ),1) ‘NonNumeric Character’,
Col1 ‘Original Character’
FROM MyTable t1
I tried it on a larger recordset:
SET STATISTICS TIME ON
SELECT PATINDEX(‘%[^0-9]%’,t1.name) ‘Position of NonNumeric Character’,
SUBSTRING(t1.name,PATINDEX(‘%[^0-9]%’,t1.name),1) ‘NonNumeric Character’,
t1.name ‘Original Character’
FROM sys.all_columns t1
ORDER BY object_id
SELECT CHARINDEX ( ‘%[^0-9]%’,t1.NAME ) ‘Position of NonNumeric Character’,
SUBSTRING(t1.name,CHARINDEX ( ‘%[^0-9]%’,t1.NAME ),1) ‘NonNumeric Character’,
t1.name ‘Original Character’
FROM sys.all_columns t1
ORDER BY object_id
how to find last non-numeric character in a string such as 10jnklgm51 (to find m)
ok i got the answer reverse the string and find the non numeric character patindex(‘%[^0-9]%’,reverse(@string))
Please remove the irritating and unnecessary popup when I move my mouse pointer out of the window. If I want to close the tab it doesn’t stop me, and if I’m switching windows to copy code it obscures the code. It is truly a pointless and irritating piece of code.
You saved my life just now! THANK YOU!