Ternary operator in SQL, where clause is placeholder

Wildcard characters

Introduction to wildcard characters

A wildcard character is a character or group of characters that can be found and replaced in a specific string. Wildcard characters are used in MySQL queries with the LIKE operator. This LIKE operator is used in the where clause of the MySQL query. With the where clause we can search for a specific pattern for a specific table column.

Example:

% wildcard is used to like% ab. It will find all ab in a particular dataset (s) that produce the output such as, over, abundance, absorbent, etc.

Why do we use WildCards?

As you are familiar with MySQL, there are various queries such as INSERT, UPDATE, SELECT, DELETE, etc. Since placeholders are used to search through a specific pattern, the SELECT statement along with the WHERE clause is included in the picture and not into other MySQL queries. To make searching with wildcards easier, let's look at a simple example of artist and his paintings. Let's say there are different paintings by different artists available at different prices in different regions of the world. Leave the database name of your choice and say "search_artist"

The table name is the artist

Here you will create a query to create the 'artist' table.

After the table is created, we will add the data to the table

Dump data for the table `artist`

Once you run the above query, the following table will be created

The percentage

Example one: Now we want to know which artist has his painting in the countries in which the country name begins with "BA".

The SEARCH query goes like this

Run the above query in MySQL

Output of the query

Example 2: Here we would like to know which artist has his painting in the countries that contain "al".

The solution is

Run the above query in MySQL

Output of the query

In the two examples above, we have a single wildcard that is '%'.

Now we are going to look for related wildcards, such as the _ wildcard (the underscore wildcard), the escape keyword, the NOT LIKE keyword, and so on

Let's look at the second placeholder, which is an underscore

_ Underline the wildcard

Example 1: This is also used with the SELECT statement in conjunction with the where clause, in which _ stands for any single character, which can be anything

The solution is

Run the above query in MySQL

Output of the query

Example two:

The solution is

Run the above query in MySQL

Output of the query above

Before diving into the third placeholder, we see the combination of the two above placeholders, the combination of the placeholders '_' and '%'

Wildcard combination importance
WHERE artist_painiting LIKE 'Mo%'Returns all values ​​that begin with "Mo"
WHERE artist_country_name LIKE '% n'Returns all values ​​that end with "n"
WHERE artist_country_name LIKE '% da%'Returns all values ​​that have a "there" in any position
WHERE artist_country_name LIKE '_o%'Returns all values ​​with an "o" in the second position
WHERE artist_name LIKE 'L% i'Returns all values ​​that begin with "L" and end with "i".

Let's look at the third placeholder, which is NOT LIKE

NOT LIKE Wildcard

This NOT LIKE operator, like others, is used with the where clause of the select statement and the underscore and percent wildcard. The rows are returned from the table in which the specified pattern does not match.

Example one: We don't want the records where the artist painting is priced at $ 600 and $ 650

The solution is :

Running the above query in MySQL with output

The fourth placeholder is (^ List of characters) at REGEXP

Example one: We want the records that contain all of the characters that match the pattern in the bracket.

The solution is :

Here the artist_country_name containing the group of characters like po leads to two records that only contain Portugal and Poland.

Running the above query in MySQL with output

The fifth placeholder is ^ NOT with the REGEXP keyword

Example: We want the records that contain all of the characters except the characters that are in the bracket.

The solution is :

Running the above query in MySQL with output

The sixth Escape keyword for placeholder

Example 1: In this example, the artist's name is the input that is searched in a table with an escape keyword for a specific pattern. The following example will illustrate this.

The solution is :

Running the above query in MySQL with output

The seventh wildcard '|'keywordto search based on specific alphabets.

Example 2: We want the records whose first names begin with the character L or S.

The solution is :

Running the above query in MySQL with output

Conclusion - wildcard characters

Hopefully with this topic you have learned% and _ placeholders with examples. You will also learn the NOT LIKE operator, the search query with the escape keyword, the (^ character list) and others. All of this was illustrated by executing a SELECT query with a where clause on the artist table. Hopefully each topic became easier to understand and grasp.

Recommended articles

This was a guide to wildcard characters. Here we've discussed what wildcard characters are, how to use wildcard characters, how to create a table, etc. You can also go through our other article suggestions to learn more -

  1. Placeholder in SQL
  2. Filters in Tableau
  3. Use of SQL
  4. T-SQL string functions
  5. Regex functions in Python (example)