The CHARINDEX function finds the position of the expression in another expression.

Syntax:

select charindex(Exptofind ,Exptosearch ,[Start_Position])

Example 1:

  1. select charindex('a','Rakesh') [Position] -- Here 'a' position is 2
  2. select charindex('H','Hello World') [Position] -- Here 'H' position is 1
  3. select charindex(' ','C# corner') [Position] -- Here [Space] position is 3
  4. select charindex('W','C# corner') [Position]--Here 'W' position is 0 beacuse there no char 'W' in search Exp
  5. select charindex('a', NULL) -- if any of the exp is null then result also null
  6. select charindex(NULL,'Rakesh') -- if any of the exp is null then result also null
  7. select charindex('o','Hello World',6)--Here 6 is the start postion of char to find the Exp
Example 2:
  1. create table Employee
  2. (
  3. EmpId int identity(1,1) primary key,
  4. Empname varchar(100),
  5. Salary int not null,
  6. Deptid int constraint Dept_Fk references Department(DeptId)
  8. )
  10. insert into Employee
  11. select 'Rakesh',8000,1
  12. union all
  13. select 'raju',1000,2
  14. union all
  15. select 'Naresh',5000,1
  16. union all
  17. select 'Venkatesh',5800,3
  19. select * from Employee
query output

Query 1:

To find the CHARINDEX position of "R" in the Empname column of the Employee table.
  1. select Empname,charindex('R',Empname) as [Position] from Employee
employee name

Query 2:

To find whose Empname(s) start with "R" using CHARINDEX.
  1. select * from Employee
  2. where charindex('R',Empname)=1
employee id