SQL Server CHARINDEX string function

SQL Server CHARINDEX string function finds first character position in a String returns integer/null/bigint

 If char found  returns first index position .
 if not returns 0;
 If input String is null returns NULL

Note:Search is case-insensitive


for Ex: get charindex of  'llo'  in 'hello'

 select charindex('llo','hello')   -->  3


Ex: Split FirstName and Last Name  using charindex  from  'Smith mathew'

declare @str nvarchar(100),@charidx int,@strLen int;
set @str='Smith mathew'
set @strLen = len(@str)
set @charidx = CHARINDEX (N' ',@str)
select left(@str,@charidx) Firstname,right(@str,@strLen-@charidx)  Lastname

O/P:

Firstname    Lastname
Smith     mathew

Tags:SQL Server CHARINDEX string function,SQL-Server charindex,SQL Server right function,SQL-Server left function,SQL Server len function,Declare variables in T-SQL