How to find underscore(_) using like operator in SQL Server

Like operator is used to match the specified pattern.

I came across the below scenario where i need to search the underscore using like operator.

Here is the example for title table

select Name from title where Name like 'santosh%'

Name

Santosh

Santosh_Kumar

Santoshkumar

select Name from title where Name like 'santosh_%'

Name

Santosh_Kumar

Santoshkumar

But I need the output only Santosh_Kumar.

So modify the SQL script like below. and output will be as expected.

select Name from title where Name like 'santosh[_]%'

Or use ESCAPE Operator

select Name from title where Name like 'santosh\_%' escape '\'

Name

Santosh_Kumar

Difference between COALESCE and ISNULL function in SQL Server

ISNULL()

Syntax: ISNULL(expression, Replace_value )

--It takes two parameter

--SQL Server Specific

--Return 1^st parameter if it is not null.

Example:

SELECT ISNULL(NULL, 'SQLServer');

Result: SQLServer

SELECT ISNULL('Inside', 'Forum');

Result: Inside

SELECT ISNULL(NULL, 10);

Result: 10

SELECT ISNULL(18, 88);

Result: 18

SELECT ISNULL(NULL, '2015-10-20');

Result: '2015-10-20'

COALESCE()

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

-It takes multiple parameters

-ANSI Standard

-Return 1st parameter if it is Not NULL.

Example:

SELECT COALESCE(NULL, 'SQL Server', 'Inside');

Result: SQL Server

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

Result: 1

Difference:

COALESCE and ISNULL function is used to return the first non-null expression.

Here is the few differences:

1. COALESCE is a ANSI standard where ISNULL is SQL Server Specific

2. COALESCE can accept multiple parameters whereas, ISNULL can accept only two parameters

declare @first as int=NULL

declare @sec as int=1234

declare @third as int=12

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam

3. COALESCE will not support two NULL Parameters while ISNULL will support

select COALESCE(NULL,NULL) as ColParam, ISNULL(NULL,NULL) as IsNUllParam

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

ISNULL depends on the length of the first datatype.

declare @first as varchar(3)=NULL

declare @sec as varchar(10)='123456'

declare @third as varchar(4)='5689'

select COALESCE(@first,@sec,@third) as ColParam, ISNULL(@first,@sec) as IsNUllParam, ISNULL(@first,@third) as IsNUllParam1