SQL BLOG: First Occurrence Of Character In a String

Scenario:

 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

Approach:

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)
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  
  
 Sample Output:

First Occurence of character in string:  911av is 4