SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

SQL Blog by Rahul Sahay

I have a total experience of 6 years primarily in databases (T-SQL and Performance Tuning). I have worked on different versions starting with SQL Server 2000 to SQL Server 2008 R2 for companies like Accenture and Airtel. I have completed Microsoft certification in MCTS (SQL SERVER). I did my MCA (Masters Of Computer Application) degree from Bangalore and have a BCA (Bachelor Of Computer Applications) from Indore.


Leave a comment on the original post [rahulsahay123.blogspot.com, opens in a new window]

Loading comments...