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