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 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 (http://blog.SQLAuthority.com)

About these ads

9 thoughts on “SQL SERVER – Find First Non-Numeric Character from String

  1. 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

  2. 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

  3. 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

  4. Dear Mr. Pinal,

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

    Waiting for your reply.

  5. 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

  6. Pingback: SQL SERVER – Order By Numeric Values Formatted as String « SQL Server Journey with SQL Authority

    • Dear Pinal,

      Can you please confirm wether there is any other method of achieving the result:

      /* First occurrence of character in a string using table variable */
      /* table variable created*/
      declare @table1 table(id integer identity(1,1),name varchar(5))
      insert into @table1 values (’911av’),(’1sdf’),(‘aaaa’)

      /* displaying content of table*/
      select * from @table1

      /* count the total number of records in the table */
      declare @rcount integer
      select @rcount = COUNT(name) from @table1

      /* declare variable for outer loop */
      declare @i_rcount integer
      set @i_rcount = 1

      /* loop that will run for each record */
      declare @srecord varchar(10)
      declare @result1 varchar(10)
      declare @x integer
      while(@i_rcount <=@rcount)
      begin
      /* store the individual records here */
      select @srecord = name from @table1 where id = @i_rcount
      select @result1 = LEN(name) from @table1
      set @x = 1
      /* checking each character in the record */
      while(@x <=@result1)
      begin
      if(ISNUMERIC(left(@srecord,@x))) 1
      begin
      select ‘First Occurence of character’ +’ ‘+ ‘in string:’+’ ‘+’ ‘+ @srecord +’ ‘+’is’+’ ‘+ cast(@x as varchar(3))
      set @x = @result1+1 /* this will exit from the inner loop */
      end
      else
      set @x = @x+1
      end
      set @i_rcount = @i_rcount+1
      end

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s