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 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:

SQL SERVER - Find First Non-Numeric Character from String patindexexample

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)

, ,
Previous Post
SQL SERVER – 2012 – List All The Column With Specific Data Types in Database
Next Post
SQL SERVER – Free Print Book on SQL Server Joes 2 Pros Kit

Related Posts

10 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • Dear Mr. Pinal,

    I have problem of using PIVOT in query. IT gives error message to set database compatibility.

    Waiting for your reply.

    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

    Reply
  • how to find last non-numeric character in a string such as 10jnklgm51 (to find m)

    Reply
  • ok i got the answer reverse the string and find the non numeric character patindex(‘%[^0-9]%’,reverse(@string))

    Reply
  • 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.

    Reply

Leave a Reply

Menu