SQL SERVER – Find First Non-Numeric Character from String - PATINDEX

SQL SERVER – Find First Non-Numeric Character from String

http://blog.sqlauthority.com/2012/10/14/sql-server-find-first-non-numeric-character-from-string/

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 numberic characterUSE tempdb
GO
CREATE TABLE MyTable (ID INT, Col1 VARCHAR(100))GO
INSERT INTO MyTable (ID, Col1)SELECT 1, '1one'UNION ALLSELECT 2, '11eleven'UNION ALLSELECT 3, '2two'UNION ALLSELECT 4, '22twentytwo'UNION ALLSELECT 5, '111oneeleven'GO
-- Use of PATINDEXSELECT 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.