But the seller is not sure about the customer’s name. He knows that the name of the client ends with two letters, os, and that it was short, about only three letters. Maybe it was something like Tos, Los, Kos etc. Using the power of a SQL wildcard, you can handle one ambiguous letter in their database. SELECT * FROM customers WHERE surname IN (‘Kos’,’Tos’,’Los’) Our query now returns all of our surnames, and we can skip writing the statement for every word combination: SELECT * FROM customers WHERE surname LIKE ‘_os’ To represent one character, we use the underscore SQL wildcard. Note that the list of surnames above is by no means exhaustive because the underscore wildcard replaces any character-even one that’s not a letter! Caution: Using SQL Wildcards Without LIKE Operator SQL wildcards only work in the LIKE operator. SELECT * FROM customers WHERE surname = ‘_os’ For example, consider this alternative query that doesn’t use the LIKE operator: If you place a wildcard inside an ordinary string that’s not an argument to the LIKE operator, you’ll find that SQL will treat that wildcard as a literal character appearing in the string. This query would search all surnames that literally equal ‘_os’, and you can bet there are no such records in our table. Using SQL Wildcards to Represent a Collection of Characters SELECT * FROM customers WHERE surname NOT LIKE ‘_os’ To get the inverse of a SQL wildcard filter, such as to find all customers whose last names are not Los, Tos, Kos, etc., you simply apply a negation, NOT, to the LIKE operator: This is a typical novice mistake, so be careful when using SQL wildcards. The underscore wildcard is not the only one available in SQL. SELECT * FROM customers WHERE city LIKE ‘%burg’ So if we’d like to list all customers who live in German cities that end in ‘burg’, we’d write the following query: A more commonly used SQL wildcard is the percent sign (%), which is used to represent one or more characters. This query will return all records for customers whose city of residence is like Hamburg, Augsburg, Oldenburg, Duisburg, and others. SELECT * FROM customers WHERE city NOT LIKE ‘%burg’ So if we’d like to extract records for customers whose city of residence does not end in ‘burg’, we’d write: Just as we negated the underscore SQL wildcard, we can also negate the percentage SQL wildcard. Note: Avoid using the percentage wildcard at the start of a string with the LIKE operator, if possible. This construction is very expensive, as the database needs to evaluate every combination of strings matching that ending pattern. The usage of % after some characters is less expensive in terms of computer resources, as the database already knows the space of strings that it needs to evaluate. It’s important to note that you can combine SQL wildcards.With the LIKE clause and a wildcard character, any string in SQL can have a single element or a group of characters replaced. When comparing strings and attempting to extract the smallest details, wildcards are helpful. The LIKE operator allows for wildcards, which help resolve complex queries. To seek a specific pattern in a column, use the LIKE operators in a WHERE clause.
0 Comments
Leave a Reply. |