SQL Wildcard Characters with examples
SQL Wildcard Characters with examples
In SQL, wildcard characters are used in conjunction with the LIKE operator to match patterns in character strings. There are two wildcard characters commonly used in SQL: % and _.
- % – The percent sign represents zero, one, or multiple characters. It can be used to match any number of characters, including zero. For example:
SELECT * FROM employees WHERE first_name LIKE 'J%';
This query will return all employees whose first name starts with the letter J.
2._ – The underscore represents a single character. It can be used to match any single character. For example:
SELECT * FROM employees WHERE last_name LIKE '_mith';
This query will return all employees whose last name ends with the string mith, where the first character of the string could be anything.
Here are some more examples of using wildcard characters in SQL:
- Match any string that contains the letter a:
SELECT * FROM employees WHERE first_name LIKE '%a%';
2.Match any string that starts with the letter a and ends with the letter s:
SELECT * FROM employees WHERE first_name LIKE 'a%s';
3.Match any string that starts with the letter a and is four characters long:
SELECT * FROM employees WHERE first_name LIKE 'a___';
The three underscores represent three wildcard characters that can be any character.
4.Match any string that ends with the letter t:
SELECT * FROM employees WHERE last_name LIKE '%t';
5.Match any string that starts with the letters sm:
SELECT * FROM employees WHERE last_name LIKE 'sm%';
Wildcard characters are useful when you want to search for data based on a pattern rather than a specific value. By combining wildcard characters with the LIKE operator, you can search for data that matches a specific pattern, even if you don’t know the exact value you’re looking for.