Wildcard Characters
These Wildcard characters help us to fetch data from a database or table by matching the pattern. We will understand each Wildcard character with examples.
We have an Article table with some sample data. We use this table to understand each Wildcard Character with the LIKE Operator.
1. Using Percent(%) WildCard
It matches one or more occurrences of character.
Query
SELECT *
FROM Article
WHERE category = 'Python' AND name LIKE '%in%'
ORDER BY submit_dt ASC;
The Result Looks Like:
Explanation: This query returns the rows having category as “Python” and “in” string in either start or middle or end and it was ordered in ascending by submission date .
2. Using Underscore(_) WildCard
It is used to match single occurrences of character.
Query
SELECT *
FROM Article
WHERE name LIKE '___i%';
The Result Looks Like:
Explanation This query returns the name whose 4th character is “i” from name column. The 3underscores(_) specifies that, there can be any character in these 3 underscores but at 4th place their should be ‘i’ present it.
3. (^Character) WildCard
Query
SELECT *
FROM Article
WHERE name LIKE '[^A-P]%' ;
The Result Looks Like:
Explanation: This query returns the name does not starts with the letters range from A to P.
4. Using [characters] WildCard
Query
SELECT *
FROM Article
WHERE category LIKE 'W[aeiou]%';
The Result Looks Like:
Explanation: This query returns the category starting with “W” then followed by any vowels specified in the query.
SQL Server LIKE Operator
The SQL Server LIKE operator is similar to the SQL LIKE Operator. It retrieves the rows by matching a string or character-specified pattern. A pattern can include regular characters wildcard characters or both. It is often used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching. It is a flexible operator that helps in finding pattern matching when you don’t know the exact pattern.
Syntax:
SELECT column1, column2
FROM table_name
WHERE col_name LIKE pattern ;
Explanation: In the above query, we have used the LIKE Operator to find the pattern in the particular column called col_name in table table_name. We will understand everything in detail in the below examples.