CHARINDEX() in SQL Server with example

 This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

expressionToFind
A character expression containing the sequence to find. Max length 8000 bytes

expressionToSearch
A character expression to search.

start_location
An integer or bigint expression at which the search starts. If start_location is not specified, has a negative value, or has a zero (0) value, the search starts at the beginning of expressionToSearch. This is an optional parameter

Note

• The first position in string is 1.
• If the expressionToFind is not found in expressionToSearch, the CHARINDEX function will return 0.
• Search is not case sensitive

Example:

1) SELECT CHARINDEX('TO', 'Customer') AS MatchPosition;

Result: 4

(Substring is not case sensitive)

2) SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition;

Result: 6

3) SELECT CHARINDEX('o', 'Rohit Techvlog', 3) AS MatchPosition;

Result: 13

(Since it started from 3rd position it returns the position of 2nd 'o')

4) SELECT CHARINDEX('z', 'Rohit Techvlog', 1) AS MatchPosition;

Result: 0

(No match found)

5) This function can be used in CASE statement

DECLARE @Name AS VARCHAR(100)= 'Find SQL articles on Rohit techvlog';

SELECT CASE

          WHEN CHARINDEX('rohit', @Name) > 0

           THEN 'Exists'

           ELSE 'Not Exists'

       END AS FindSubString;

Result: Exists

6) This function can be used in a output of existing data in a table.

select JobTitle,CHARINDEX('p', JobTitle) as position_of_p

from Employee

Result:

COALESCE function in SQL Server with example

-It takes multiple parameters

-ANSI Standard

-Return 1st parameter if it is Not NULL.

Syntax: COALESCE( expression1, expression2, ... expression_n )

Example:

1) SELECT COALESCE(NULL, 'SQL Server', 'Rohit techvlog');

Result: SQL Server

SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4);

Result: 1

2) It accepts multiple parameter and returns first Not NULL argument

declare @first as int=NULL
declare @sec as int=1234
declare @third as int=12
select COALESCE(@first,@sec,@third) as ColParam

Result: 1234

3) COALESCE will not support NULL parameter as replacement argument

select COALESCE(NULL,NULL) as IsNUllParam

Error Output:

At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

4) COALESCE will not depend on the length of the datatype, it will return the whole string

declare @first as varchar(3)=NULL
declare @sec as varchar(10)='123456'
declare @third as varchar(4)='5689'
select COALESCE(@first,@sec) as IsNUllParam

Result: 123456

SQL Server ISNULL function in detail with example

The ISNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression

The following shows the syntax of the ISNULL() function:

ISNULL(expression, replacement)

The ISNULL() function accepts two arguments:

expression In this parameter, we specify the expression in which we need to check NULL values.

replacement is the value to be returned if the expression is NULL. The replacement must be convertible to a value of the type of the expression.

The ISNULL() function returns the replacement if the expression evaluates to NULL. Before returning a value, it implicitly converts the type of replacement to the type of the expression if the types of the two arguments are different.

In case the expression is not NULL, the ISNULL() function returns the value of the expression.

Example:

1) Return the 2nd argument if the first string is NULL value

SELECT ISNULL(NULL, 'SQLServer') result;

Since first string is not NULL value it will return 1st argument

SELECT ISNULL('Rohit techvlog', 'SQLServer') result;

2) ISNULL will support NULL parameter as replacement argument

select ISNULL(NULL,NULL) as IsNUllParam

Output

NULL

3) ISNULL depends on length of first datatype

declare @first as varchar(3)=NULL
declare @sec as varchar(10)='123456'
declare @third as varchar(4)='5689'
 select ISNULL(@first,@sec) as IsNUllParam, ISNULL(@first,@third) as IsNUllParam1

    

Difference between IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in sql server with example

Scope_Identity() returns value generated for the same session and same scope. This is the most common way to find.

@@IDENTITY returns value generated for the same session and  across any scope

IDENT_CURRENT returns value generated for across any session and any scope.

Note: Scope is a module; a Stored Procedure, trigger, function, or batch

Example:

create table Test1(

ID int identity(1,1),

value varchar(30)

)

create table Test2(

ID int identity(1,1),

value varchar(30)

)

insert into Test1

values ('RK'),('MK')

select SCOPE_IDENTITY()

select @@IDENTITY

Now create a trigger in Test1 table

create trigger insertonTest1 on Test1 for Insert

as

Begin

insert into Test2 values ('GK')

End

Now insert new record to Test1 table

insert into Test1

values ('SHG')

Now the below scripts on the same session

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('Test1')

select IDENT_CURRENT('Test2')

Now you can see @@IDENTITY returned the value from different scope. Also IDENT_CURRENT returns as per table name.

Now run the below query on different window means different session.

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('Test1')

select IDENT_CURRENT('Test2')

Since SCOPE_IDENTITY and @@IDENTITY run on different session returned NULL But as the same time IDENT_CURRENT returns the value irrespective of session and scope

You can find other details of Identity columns Below

• Identity Column in SQL Server

• How to find last generated identity column value in SQL Server

How to find last generated identity column value in SQL Server

Please go through the below before proceeding:

Identity Column in SQL Server

For retrieving identity column values first create a table. 

Create a table using Identity Key:

CREATE TABLE tblPerson (

    [Person_id] INT IDENTITY(1,1) PRIMARY KEY,

    [Name] varchar(50),

    [Gender] CHAR(1)

);

INSERT INTO tblPerson([Name], [Gender])

values

('John Doe','M'),

('Kimi Sharma','F')

We can get last generated Identity values in two ways

Using function SCOPE_IDENTITY() and global variable @@IDENTITY

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('tblPerson')

Output: 2

Scope_Identity() returns value generated for the same session and same scope. This is the most common way to find.

@@IDENTITY returns value generated for the same session and  across any scope

IDENT_CURRENT returns value generated for across any session and any scope.

You can find the difference Here:

Difference between IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in sql server with example