Skip to main content

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



Labels:

Comments

Post a Comment

Popular posts from this blog

Coalesce function

Coalesce function returns the first non-null value among the arguments. Syntax: Coalesce (expression [,..n]) Here is example using Coalesce function Example 1 DECLARE @Str1 varchar ( 10 ), @str2 varchar ( 20 ), @Str3 varchar ( 20 ) SET @Str2 = 'Sql' , @Str3 = 'Server' SELECT COALESCE ( @Str1 , @str2 , @Str3 ) As [Coalesce] In above example @Str2 value is ‘Sql’ , @str3 value is ‘Server’  and @str1 values is Null because it not assigned any value . Output: It return’s “Sql” because Coalesce function return’s first non null value. Example 2: Coalesce in select statement. IF OBJECT_ID ( 'Employee' , 'U' ) IS NOT NULL DROP TABLE Employee CREATE TABLE Employee (   ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY ,   NAME VARCHAR ( 20 ),   SALARY INT ) INSERT INTO Employee   ( NAME , SALARY ) VALUES ( 'Rakesh' , 5000 ),(NULL, 6000 ),( 'Naresh...
1 comment
Read more

Variables in T-SQL

Variables can be used to store the data in temporally based on data type. Variable name begin with @ symbol. There are two types of variables in T-SQL. 1.   Local Variables (It must begin @ symbol). 2.   Global Variables (it must begin @@ symbol) this variables also known as system variables. Syntax Declare a Variable DECLARE   @Variable-Name   DATATYPE EXAMPLE: DECLARE   @Name   VARCHAR ( 100 ) In before 2008 versions of SQL SERVER we declare a variable and assign a value in 2 lines.                 DECLARE   @Name   VARCHAR ( 100 ) SET   @Name = 'LeadFirstSQL' In 2008 and Later versions of SQL SERVER we declare a variable and assign a value in single line. DECLARE   @Name   VARCHAR ( 100 )   = 'LeadFirstSQL' Variables must be declare and used with the same batch. DECLARE   @Name   VARCHAR ( 100 ) ...
Post a Comment
Read more

CONCAT FUNCTION IN SQL SERVER 2012

Concat function is used concatnating the values among the different arguments. Syntax: Concat([String1],[String2]..[StringN]);  DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT CONCAT ( @FName , ' ' , @LName ) as [FullName] ; Output: Rakesh Kalluri --Before SQL 2012 Versions we can use like this way DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT @FName + ' ' + @LName as [FullName] ; Output: Rakesh Kalluri --But Before SQL 2012 Versions there are 2 problems when we  contacting . --1.Need to replace NULL Value to Empty.if we directly concat NULL Value the  Entire  result also NULL. --Before 2012: DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SELECT @FName + ' ' + @LName a...
Post a Comment
Read more