How to search wildcards character in string

If you need to search for actual underscore or percent characters in a string, you can use the
ESCAPE option to identify those characters.
For example,

'%\%%' ESCAPE '\'

The character after the ESCAPE tells the database how to differentiate between characters to
search for and wildcards, and in the example the backslash character (\) is used.
The first % is treated as a wildcard and matches any number of characters;
the second % is treated as an actual character to search for;
the third % is treated as a wildcard and matches any number of characters.

The following query uses the promotions table, which contains the details for products being
discounted by the store .
The query uses the LIKE operator to search the name column of the promotions table for the values whatever starts or ends with '%' character.

SELECT name
FROM promotions
WHERE name LIKE '%\%%' ESCAPE '\';

The results would be as shown below,

NAME
------------------------------
10% off Z Files
20% off Pop 3
30% off Modern Science

Comments

Post a Comment

Popular posts from this blog

SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time. Here I have created a simple Function for this. Create Function Trim(@mText varchar(MAX)) Returns varchar(MAX) AS Begin return LTRIM(RTRIM(@mText)) End You can run this function as shown below here, Select dbo.Trim(' Test ') So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.
Read more
SQL Server/Oracle/MySQL Union query Many time we require results from different tables or different queries. We try one after the other and list the result accordingly. There is a better way to do this by using UNION command or query. We can list records of two different tables by combining two sql queries to one. What is the advantage ? We can as well write the query two times and display one after the other. The point here is we can pick up unique records also. Say we have two tables one is storing name of the football players and other is storing players of baseball team. We want to display a list of players who are member of one of the team at least. Here we can display the name of the players by using UNION command. If John is member of football and baseball team then his name will appear once only. If we want to display all the records then we can use ALL along with the UNION command. Here are two tables with three records each. Please note that there is one common record
Read more