Charindex function in sql server

Charindex is used to find the position of the expression in another expression.

Syntax:

select charindex(Exptofind ,Exptosearch ,[Start_Position])

Examples:

select charindex('a','Rakesh') [Position] -- Here 'a' position is 2

select charindex('H','Hello World') [Position] -- Here 'H' position is 1

select charindex(' ','C# corner') [Position] -- Here [Space] position is 3

select charindex('W','C# corner') [Position]--Here 'W' position is 0 beacuse there no char 'W' in search Exp

select charindex('a', NULL) -- if any of the exp is null then result also null

select charindex(NULL,'Rakesh') -- if any of the exp is null then result also null

select charindex('o','Hello World',6)--Here 6 is the start postion of char to find the Exp

Example-2:

create table Employee

(

 EmpId int identity(1,1) primary key,

 Empname varchar(100),

 Salary int not null,

 Deptid int  constraint Dept_Fk references Department(DeptId)

)

insert into Employee

select 'Rakesh',8000,1

union all

select 'raju',1000,2

union all

select 'Naresh',5000,1

union all

select 'Venkatesh',5800,3

select * from Employee

Query -1

To find ‘R’ Charindex position from Empname column from Employee table.

select Empname,charindex('R',Empname) as [Position] 

from  Employee

Query-2

To find Whose Empname starts with name ‘R’ using Charindex

select * from Employee

where charindex('R',Empname)=1