Friday, March 1, 2013

SQL BLOG: First Occurrence Of Character In a String


 We need to find the first occurrence of character ie non numeric data in the string without using the built in function.
Example: String 577a     
First occurrence of character : 4


i)                    Here I haven’t used any built in functions. First we need to find the number of records in the table.
ii)                   Now for each record, calculate the length of string. This operation is performed by WHILE loop
iii)                 Now for every single record, I have checked wether the character is numeric or non numeric. In case of non numeric, return the current position as the first occurrence otherwise increment the counter by 1

Full code is below:

/* 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)
/* 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)
   if(ISNUMERIC(left(@srecord,@x))) <> 1
        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 */
        set @x = @x+1
   set @i_rcount = @i_rcount+1
 Sample Output:

First Occurence of character in string:  911av is 4


  1. You can do this much simpler with

    select *, patindex('%[^0-9]%', name) from @table1

    1. Hi Kevan,

      I have tried building this solution without use of built in functions. Definitely we can use patindex to get the output.

    2. I wasn't sure what you meant by not using built-in functions as you are using LEN() and ISNUMERIC() in your solution.

      Also be careful using ISNUMERIC() as this determines whether a string can be converted into a numeric data type, not necessarily if the data is purely numeric - for example

      select isnumeric('£')

      returns true

  2. Dear Web site owner. My partner and i actually enjoy this post and the internet site all in all! Your piece of writing is really plainly composed as well as simply understandable. Your current Blog design is awesome as well! Would be awesome to know where I are able obtain it. Please maintain up the very good job. We all require far more such website owners like you on the net and much fewer spammers. Fantastic mate!