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