The most common character that we escape in SQL Server when it comes to string literals is a single quote itself. We could escape that by doubling itself like

--Find text 'P'roduct'
SELECT ProductDescriptionID,[Description] 
FROM Production.ProductDescription
WHERE [Description] LIKE 'P''roduct';

But when we are dealing with wild characters such as %, _, [] or [^] SQL Server let us define our escape character for them and use ESCAPE key word to tell what the escape character is. Let us see the following examples

--Find text '%Product'
SELECT ProductDescriptionID,[Description] 
FROM Production.ProductDescription
WHERE [Description] LIKE '\%Product' ESCAPE '\';
 
--Find a text 'Product_'
SELECT ProductDescriptionID,[Description] 
FROM Production.ProductDescription
WHERE [Description] LIKE 'Product$_' ESCAPE '$';
 
--Find text 'Product[0]'
SELECT ProductDescriptionID,[Description] 
FROM Production.ProductDescription
WHERE [Description] LIKE 'Product![0!]' ESCAPE '!';

In the above examples I used (\, $, !) characters to demonstrate SQL Server let us define any character as our escape character. Since it is common to use back slash ‘\’ as escape character in many other programming characters it will make our script more readable if just stick to that.