SQL WILDCARD Characters

A wildcard character can be used to compare a value to similar values or substitute for any other character(s) in a string.

To match a pattern from a word, special characters and wildcards characters may have used with SQL LIKE operator.

The SQL LIKE operator can be used with in any valid SELECT SQL statement.

SELECT * FROM Books 
WHERE 
BookName LIKE 'M%';

SELECT * FROM Books 
WHERE 
BookName LIKE '%ing';

SELECT * FROM Books 
WHERE 
BookName LIKE '%one%';

SELECT * FROM Books 
WHERE 
BookPrice LIKE '1_0';

SELECT * FROM Books 
WHERE 
BookPrice LIKE '__5';

SELECT * FROM Books 
WHERE 
AuthorName LIKE '[PS]%';

SELECT * FROM Books 
WHERE 
AuthorName LIKE '[A-D]%';

SELECT * FROM Books 
WHERE 
DomainName LIKE '[!DSP]%';

When referencing tables with an alias, use the wildcard character(*).

s.* aliases the staging table

I.* aliases a temporary table and provides ROWID_OBJECT, PKEY_SRC_OBJECT, and ROWID_SYSTEM information for the records being updated. The i alias can only be used for ROWID_OBJECT, PKEY_SRC_OBJECT, and ROWID_SYSTEM columns in a validation rule.

Example 1: Instead of explicitly aliasing each field, combine two tables with a handful of identical column names and use a wildcard like:

SELECT tablename.* AS  alias.*

Example 2:

SELECT t1.*, alias2.*, alias3.*
  FROM t1, 
   t2 AS alias2, 
   t3 AS alias3

After you've defined the table alias, you can use table alias.* in the SELECT. However, without a unique column alias, getting the correct address/etc information will be a hassle.

The Informix® modification to the ANSI/ISO-standard for SQL uses the backslash () symbol as the default escape character (when DEFAULTESCCHAR is not defined). Set the DEFAULTESCCHAR value to the backslash (\) symbol or another ASCII character to make that character the default escape character. See the DEFAULTESCCHAR session environment option for further information.

\ is a special character known as an escape character that signifies that the character immediately after it should be taken literally (useful for single quotes, wildcard characters, etc.).

In a Locate, the backslash is an escape character, so if you need to find one, enclose it in square brackets and 'escape' it — i.e. [\\] in a Find.

In Replace, the backslash is also a unique character because it marks the element you wish to replace \ with itself. Rather than a, you must substitute the number ^92.

Example 1: The following condition tests the description column for the string tennis, alone or in a longer string, such as tennis ball or table tennis paddle:

WHERE description LIKE '%tennis%' ESCAPE '\'

Example 2:

SELECT txt1 FROM T1 WHERE txt1 LIKE '_a%'

will select records with txt1 values of 'xa1', 'xa taco', 'ya anything really', etc.

A case-insensitive string is stored in a SQL table column. Instead of equal, the percent wildcard is used in both cases to return records that include the string.

Example 1: Upper or lower

In the first scenario, you must modify the column to upper or lower case, then search for your string.

SELECT * FROM table WHERE upper(column_name) LIKE '%ABC%'

Example 2: find all car models that start with the letter "C".

SELECT * FROM cars
WHERE model LIKE 'C%';

Results from the cars table:

The 3 of the 6 entries in our automobiles table begin with the letter "C," as can be seen.

Because the SQL LIKE statement is case-insensitive, 'C percent' and 'c percent' will get the same results.

To discover items that conclude with a character or characters, we can utilise the % wildcard and the SQL LIKE expression.

Example 1: The CASE with Wildcard look like this:

SELECT
CASE

WHEN CARS.Model LIKE '%Ford%' THEN 'Ford'
WHEN CARS.Model LIKE '%Chev%' THEN 'Chev'
WHEN CARS.Model LIKE '%Buick%' THEN 'Buick'

ELSE 'All Cars'
END AS 'Models'

Example 2: Instead of putting this in a WHERE clause, alter your SQL to use a JOIN on your SELECT statement.

INSERT INTO Foundation.TaxLiability.EmpowerSystemCalendarCode

SELECT SystemTax.SystemTaxID,
  EmpowerCalendarCode.CalendarCodeID
  ,CASE WHEN  EmpowerCalendarCode.CalendarName LIKE '%Monthly%' THEN 3
   WHEN  EmpowerCalendarCode.CalendarName LIKE '%Annual%' THEN 2
   WHEN  EmpowerCalendarCode.CalendarName LIKE '%Quarterly%' THEN 4
  ELSE 0
  END
FROM Foundation.Common.SystemTax SystemTax
INNER JOIN Foundation.TaxLiability.EmpowerCalendarCode EmpowerCalendarCode
 ON SystemTax.EmpowerTaxCode = EmpowerCalendarCode.LongAgencyCode 
 AND SystemTax.EmpowerTaxType = EmpowerCalendarCode.EmpowerTaxType

Example 3:

The SQL statement below extracts all employees with phone numbers that are formatted as three-three-four digits with dashes in between (999-999-9999). To determine if the pattern is domestic or foreign, it is matched to the phonenumber column.

To determine the phone category type, the case expression is assessed for the specified pattern.

SELECT p.FirstName, 
  p.LastName, 
  PhoneNumber,
  CASE WHEN ph.PhoneNumber LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' then 'Domestic Phone Number'
  ELSE 'International Phone number'
  END PhoneNumber
FROM Person.PersonPhone AS ph
  INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
ORDER BY p.LastName;

The number is categorised as domestic or international in the example above. The LIKE operator is used to assess the phonenumber column using the square bracket. The first character position is examined for matching pattern zero to nine, the second character position can be any number from zero to nine, the third and fourth character positions must be dashes, and the remainder of the characters follow the same rationale.

A wildcard character is a character that can be used to replace any other character(s) in a string. In a WHERE clause, the LIKE operator is used to look for a certain pattern in a column.

When searching for complex data, we employ SQL wildcards. Strings or numerical data with particular features could be compromised by this complex data.

When we want to speed up our querying procedure, we can use wildcards as well. When wildcards are used, the results are significantly faster.

Pattern matching is more versatile when wildcard characters are used with the LIKE operator because wildcard characters can be compared with any pattern of characters. SQL Server supports wildcard characters:

• Percent %
• Underscore _
• Brackets []
• Caret [^]

The Percent Wildcard Character (%):

Any string of zero or more characters is represented by the Percent Wildcard Character.

The Underscore Wildcard Character (_):

Any single character can be represented by the Underscore Wildcard Character.

The underscore ('_') is a wildcard character and not a regular character for the LIKE clause. Wildcard characters, unlike literal characters, have a specific definition for the LIKE operator.

Bracketed Characters []:

Brackets are used to find any single character within the range [a-c] or set [abc].

The Caret Wildcard Character [^]:

The Caret Wildcard Character is used to find any single character that does not belong in the supplied range [^a-c] or set [^abc].

The wildcards will be write like below:

Example 1: Percent

For example, to find all employees with the first name starting with 'J' in the Person.Person table, use this query:

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE 'J%'

Let us find the details of locations with the first character as ‘P’ and the last character as ‘e’.

SELECT * FROM dataflair_emp2
WHERE location LIKE 'p%e';

The following SQL statement selects all customers with a City starting with "ber":

SELECT * FROM Customers
WHERE City LIKE 'ber%';

Example 2: Underscore

To find all employees with the first name that is 3 letters long and is starting with 'Jo':

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE 'Jo_'

Example 3: Brackets

To find all employees with a 3 characters long first name that begins with any single character between 'J' and 'Z' and ends with 'oe':

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE '[J-Z]oe'

Example 4: Caret

To find all employees with a 3 characters long first name that begins with 'Ja' and the third character is not 'n':

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE 'Ja[^n]'

Example 5: unserscore and percent

For pattern matching, wildcard characters included in brackets are treated as literal characters, therefore we can rephrase the query in the example as follows to get the proper answer:

USE TestDB
GO

SELECT *
FROM myUser
WHERE LoginName LIKE '%[_]my%'

To use wildcards in MySQL column names, first choose column name and then implement the LIKE operator to the wildcard.

Syntax:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ’yourTableName’
AND COLUMN_NAME LIKE 'yourWildCards%';

Example 1:

We have four fields and out of which three of them begin from the word Product. Now apply the wildcards in column names. The query is as follows :

select column_name from information_schema.columns
where table_name = 'WildcardDemo'
and column_name LIKE 'product%';

Output:

Example 2: Only exact matches of the column name will be found by the query. Instead of using LIKE and percent wildcard characters, we can utilise LIKE and percent wildcard characters to identify partial matches:

SELECT
  sys.columns.name AS ColumnName,
  tables.name AS TableName
FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = tables.object_id
WHERE
  sys.columns.name LIKE '%ColumnName%'

A simple query that returns all tables and associated columns that include a specific (or similar) column name.

Example 3: column name specified is a wildcard character (*), the content of that column is inserted as if there is no column name specified. If this column is a non-xml type column, the column content is inserted as a text node, as shown in the following example:

USE AdventureWorks2012;  
GO  
SELECT E.BusinessEntityID "@EmpID",   
       FirstName "*",   
       MiddleName "*",   
       LastName "*"  
FROM   HumanResources.Employee AS E  
  INNER JOIN Person.Person AS P  
    ON E.BusinessEntityID = P.BusinessEntityID  
WHERE E.BusinessEntityID=1  
FOR XML PATH;  

Output:

Example 4:

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'Foods'
  AND table_schema = 'YourDB'
  AND column_name LIKE 'Vegetable%'

Example 1: passing 2 values in variable at runtime @barcode @year but if i run the query with values type explicitly in sql editor it works fine and return values

SELECT barcode
 , Date
 , timein
 , timeout
 , totaltime
 , leave
 , remarks
FROM TimeSheet
WHERE barcode = 123456
 AND Date LIKE '2013-07-%'

Example 2: For instance, a query that selects items with a date that matches the current month.

This indicates that any day between June 1 and June 30 is acceptable. To accomplish so, I'll need a wildcard like "percent" to replace the date field's actual day.

select * from item where date = '2000-06-%%';

Example 3: Query to view date from the table created using wildcard:-

The given below Query returns you the records or rows from the table 'employee1' in which date begins with 20.

select * from employee1 where date like '%20';

Output:

Insert the ESCAPE character after the ESCAPE keyword in the LIKE predicate. If the pattern has no percent signs or underscores, it simply reflects the text; in this instance, LIKE functions as the equals operator. A single letter is represented by an underscore (_), while a sequence of zero or more characters is represented by a percent sign (%).

If the string matches the specified pattern, the LIKE expression returns true. (The NOT LIKE statement returns false when LIKE returns true, and vice versa.) NOT is an equivalent expression (string LIKE pattern).

The LIKE pattern matches the complete string every time. As a result, the pattern must begin and end with a % sign to match a sequence anywhere inside a string.

Syntax:

string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]

Some examples:

'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false

Example 1: Let's say you know at least a handful of the database users have a favourite quote that contains a percentage sign, but you don't know who they are.

You could try running the following query:

SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%';

However, this query won’t be very helpful. Because the percentage sign serves as a stand-in for any string of characters of any length, it will return every row in the table:

To escape the percentage sign, you can precede it with a backslash (\), MySQL’s default escape character:

SELECT * FROM user_profiles WHERE quote LIKE '\%';

However, this query won’t be helpful either, since it specifies that the contents of the quote column should only consist of a percentage sign. Hence, the result set will be empty:

Output:

Example 2:

wildcard character, % or _ we need to escape these characters. In MySQL, we can do this by inserting a backslash in front of the wildcard character, e.g. using addcslashes($foo, '\%_'). AFAICT SQL-92 says that there is no escape character and that backslash should be treated like any other character, but this is only enforced in the NO_BACKSLASH_ESCAPES mode.

In order to ensure consistent behaviour across databases we need to explicitly specify an escape character in our SQL, i.e. like this:

SELECT * FROM bar WHERE foo LIKE 'abc\\_def' ESCAPE '\\'

Though any character can be used, I suggest using backslash as this is the default on MySQL and PostgreSQL.

Example 3: In this case we are going to use ‘\’ as the escape character

SELECT * 
FROM users 
WHERE username like '%\[%' ESCAPE '\'

In the LIKE clause, the square bracket is one of the wildcard characters.

The LIKE clause in SQL is used to match patterns using wildcard operators such as %, ^, [], and others. If we use a LIKE clause with a string of square brackets to filter the record, we will not receive the intended results.

Example 1: The use of square brackets, such as [], allows us to recognise many single characters in a given spot. Let's imagine you want to list all the rows where the first name's third character begins with I or K. We can put the pattern matching set in the third position and close it in the square instead of putting several LIKE conditions. The query engine looks for the letters 'I' and then 'K'.

Let’s execute the following SQL statement

SELECT p.FirstName, 
  p.LastName, 
  PhoneNumber
FROM Person.PersonPhone AS ph
  INNER JOIN Person.Person AS p ON ph.BusinessEntityID = p.BusinessEntityID
WHERE ph.PhoneNumber LIKE '7_8%' and p.lastname like 'Ba[ik]%'
ORDER BY p.LastName;

Example 2: The first situation entails searching for an open bracket, such as '['. As you can see from the table, this character appears in numerous rows.

SELECT * 
FROM users 
WHERE username like '%\[%' ESCAPE '\'

The second scenario consist in looking for consecutive open brackets ‘[‘. For example if we look into the table for the following characters ‘[[[‘

It seems like we would need to apply the escape character for each open bracket as follows.

SELECT * 
FROM users 
WHERE username like '%\[\[%' ESCAPE '\'

Example 3: Romy[R]kumari is a string value in a table. When we try to apply LIKE 'Romy[R]%' 'to the select statement, nothing happens.

>Explanation:

Square brackets[], is among the wildcard operators used in SQL with the LIKE clause. It is used to match any single character within the specified range like ([b-h]) or set ([ghijk]).

We can escape square brackets using two methods:

• Escape using one more square bracket
• Escape using Escape character

using extra bracket Syntax:

For matching ‘ROMY[78]’

SELECT *  
  FROM table_name 
  WHERE column_name LIKE 'ROMY[[]78]%'

Example 4: In this method, you just need to escape the opening square bracket ‘[‘ with square brackets ‘[]’. Example:

SELECT * 
   FROM MTB_Table_A 
   WHERE [Name] LIKE 'WD[[]C]%'
GO

NOTE: After escaping the opening square bracket, there is no need to escape the closing square bracket ']' because it has no specific value.

Returns a table output that accurately represents a single character in any position.

Syntax:

The basic syntax for using this wildcard character is:

SELECT * FROM tab_name WHERE column_name LIKE 'X_';  
OR,  
SELECT * FROM tab_name WHERE column_name LIKE '_X';  

In the above syntax, the 'X' specifies any string pattern, and _ symbol matches exactly one character.

Example 1: This statement returns all employees whose age belongs to 40 to 49:

SELECT * FROM employees WHERE emp_age LIKE '4_';  

Example 2: consider the CUSTOMERS table having the following records.

The code block below is an example of how to display all records from the CUSTOMERS table where the SALARY begins with 200.

SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';

Output:

Example 3: To match exactly one character, use the underscore wildcard. Assume we wish to find all movies released in the year 200x, where x is a single character with any value. To accomplish this, we'd employ the underscore wildcard. All of the films released in the year "200x" are listed in the script below.

SELECT * FROM movies WHERE year_released LIKE '200_';

Output:

In our result set, only movies with 200 followers by any character in the field year released were retrieved. This is because the underscore wildcard recognized any single character in the pattern 200.

Example 4: use the LIKE operator, for example, if we want to get the values that includes (and/or end with) our numbers, we may do the following:

SELECT * FROM MyTable WHERE CAST (MyColumn as CHAR) LIKE '123%' 

We'll begin by understanding the symbols and fundamental grammar of wildcard regular expressions. Character sets and repetition will be used.

select * from FOOS where FOOID like 2    
// and 
select * from FOOS where FOOID = 2

The SQL WILDCARD Operators are combined with the LIKE operator to improve the search pattern string from the database table. To find a pattern in a word using special characters and wildcards.

Wildcard characters can function as both a prefix and a suffix. Wildcard operators can be used wherever in Word (prefix, suffix, and everything in between). In a term, there can be more than one wildcard.

There are two wild card operators in SQL that are used in conjunction with the LIKE operator to make finding more effective.

• % (percentage) operator
• _ (underscore) operator

When searching, wildcard operators are used to replace for one or more characters.

% (Percentage) – We can check from 0 to many characters in a string or column's value with this wildcard operator.

_ (Underscore) – Only one character in the value of a string or column can be matched with this wildcard operator.

[char_list] – We can define several characters in 'char_list' to search and match in a given text using this syntax.

Example 1: SQL wildcards are used with SQL LIKE operator.

SELECT *
FROM agents
WHERE agent_name  NOT LIKE 'M%';

Example 2: SQL LIKE condition with _ (underscore) WILDCARD character to matches any exactly single character with in string.

SELECT * FROM users_info WHERE name LIKE 'Pa_l S__gh';

Output:

Example 3: Display only those employees whose name ends with the letter a. SQL statement is.

SELECT *
FROM Employee
WHERE EmployeeName LIKE '%a';

Note: SELECT * is used here to select all columns in the data base table at the same time. It saves time compared to listing each column individually.

Example: use the LIKE operator and specify your pattern executing a query like this one:

SELECT mydata FROM mytable
WHERE(mydata LIKE 'ABCD [1-9]' OR mydata LIKE 'ABCD 1[0-9]' OR mydata LIKE 'ABCD 20') 
AND mydata NOT LIKE 'ABCD [4-8]';

or, something more concise and shorter:

SELECT mydata FROM mytable
where mydata like 'ABCD [^4-8]%';

Percent sign ( %) - A series of 0 (zero) or more characters is represented by the percent sign character (%).

Note: MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.

Syntax:

The basic syntax of a '%' is as follows.

SELECT * FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT * FROM table_name
WHERE column LIKE '%XXXX%'

For Example:

Example 1: The following example uses the LIKE operator to find all employees whose first names start with Da :

SELECT employee_id,
	first_name,
	last_name
FROM
	employees
WHERE
first_name LIKE 'Da%';

Output:

Example 2: The following query returns customers whose first name contains er string like Jenifer, Kimberly, etc.

SELECT first_name,
        last_name
FROM
	customer
WHERE
	first_name LIKE '%er%'
ORDER BY 
   first_name;

Example 3: The 'cust_name' must begin with the letter 'S', the following SQL statement can be used :

SELECT cust_code,cust_name,cust_city,cust_country
FROM customer
WHERE cust_name LIKE 'S%';

Output:

Example 4: consider the CUSTOMERS table having the following records.

The following code block is an example, which would display all the records from the CUSTOMERS table where the SALARY starts with 200.

SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';

Output:

? (Question Mark Character) - It has the capability of acting as a wildcard operator.

Because the behaviour of the question mark character wildcard is deactivated by default, it is omitted when used in queries. When enabled, the question mark character at the end of a term or within a term functions as a wildcard operator, allowing you to define a term that can be filled by any character in its place.

In the day-of-month and day-of-week fields, a question mark (?) is permitted. It's used to declare "no specific value," which is beneficial when you only need to specify something in one of these two fields.

Use a question mark to indicate that any nonblank character, regardless of type, must occupy a specified location.

Gr?y retrieves entries including phrases such as grey or grey when the wildcard behaviour is allowed.

Example:

JOB?A% matches all jobs that have JOB in the first three positions, any character in the fourth, an A in the fifth, and a number in the last position. JOB1A6 and JOB#A7 are matches, but JOB1Z is not.

A single character that is not in the provided character list or range is represented by square brackets with a caret sign (^) followed by a character list or range (such as [^A-E]).

In SQL, use this character range wildcard to look for a series of alphabets or other characters.

Example 1: Query returns all Customers from the Customers table whose Last name starts with any Letter between M and Y.

SELECT [EmpID]
  ,[FirstName]
  ,[LastName]
  ,[Education]
  ,[Occupation]
  ,[YearlyIncome]
  ,[Sales]
  FROM [Customer]
  WHERE [LastName] LIKE N'[M-Y]%'

Example 2: The following query returns employees where the first character in the last name is not a letter in the range A through E.

SELECT empid, lastname
FROM HR.Employees
WHERE lastname LIKE N'[^A-E]%';

Output:

Example 3: find the details of locations with the first character as ‘P’ and the last character as ‘e’.

SELECT * FROM dataflair_emp2
WHERE location LIKE 'p%e';

Here we can see the details of locations starting with ‘p’ and ending with ‘e’.

Example 4: returns all employees from the table whose city does not start with 'a' and ends with 'a' character, execute the below statement:

SELECT * FROM employees WHERE city NOT LIKE 'a%a';

Replace any character other than 0-9 with an empty string in a phone_number field.

Example 1: Is there a replace function in Microsoft SQL Server that uses wild cards? Regular Expressions, I gather, are not natively supported.

I note that there is a PATINDEX function which can be used to bolt together a solution — is there one something which does it more simply?

For example REPLACE(data,'[xy]','q') to replace x or y with q.

Patterns and wildcards are not supported by the REPLACE built-in function; only LIKE and PATINDEX do.

If you want only to replace a single character, as mentioned in the question, you can call REPLACE twice, one nested inside the other, as seen below:

SELECT REPLACE(
  REPLACE('A B x 3 y Z x 943 yy!',
   'x',
   'q'),
   'y',
   'q');

Returns:

---

Example 2: Update this table to remove non-numeric characters from the phone_number field, something like that:

update PHONE_TABLE
set phone_number = replace(phone_number, [^0-9], '')

To match a pattern from a word or string, use the underscore character ( ). To match a pattern of numerous characters, you can use more than one (_) underscore character.

The LIKE SQL operator defines the ESCAPE clause, which allows you to specify a character that will escape the next character in the pattern.

Two wildcards are employed, similar to predicate queries: " % " and "_" where " percent " means zero or more characters and "_" indicates any character, but if the query condition expression is " " it means any character. The characters " percent " and "_" must be escaped if they appear in the file.

For your case, the following WHERE clauses are equivalent:

WHERE username LIKE '%[_]d'; 
WHERE username LIKE '%$_d' ESCAPE '$';
WHERE username LIKE '%^_d' ESCAPE '^';

I had a similar issue using like pattern '%_%' did not work - as the question indicates :-)

Using '%\_%' did not work either as this first \ is interpreted "before the like".

Using '%\\_%' works. The \\ (double backslash) is first converted to single \ (backslash) and then used in the like pattern.

Example 1: Replace _ with \_

String a="aaa_";
a=a.replace("_", "\\_");
System.out.println(a);

In this way, if you use a to like in the future, the underscore will not be considered as a wildcard.

Example 2: To get 'cust_code', 'cust_name', 'cust_city' and 'cust_country' from the table 'customer' with following conditions:

the 'cust_name' must initiated with the letter 'R',

the third letter of 'cust_name' must be 'm',

and the second letter of 'cust_name' may be any, the following sql statement can be used :

SELECT cust_code,cust_name,cust_city,cust_country
FROM customer
WHERE cust_name LIKE 'R_m%';

Output:

Example 3: Underscore or percentage sign, we could write:

SELECT * FROM partno WHERE part LIKE '%_%' 

However, this can be taken as any number of characters, a single character, and any number of additional characters. We must inform SQL that the underscore should be treated as an underscore rather than a wildcard.

The LIKE clause has an ESCAPE expression where we can specify the character we want to use to escape the wildcard. If we use the slash character before the underscore, the following code works flawlessly:

SELECT * FROM partno WHERE part LIKE '%\_%' ESCAPE '\'

If only a portion of a text value is known, use WHERE LIKE. In a lot of examples, it allows two wildcard alternatives in the WHERE portion, which has various LIKE clauses using the '%' and '_' operators.

The LIKE statement is used in conjunction with the WHERE clause to create a match pattern condition using wildcard characters. There are many wildcard characters that have distinct meanings.

Syntax:

SELECT statements... WHERE fieldname LIKE 'xxx%';

“SELECT statement…” is the standard SQL SELECT command.

“WHERE” is the key word used to apply the filter.

“LIKE” is the comparison operator that is used in conjunction with wildcards

Example 1: consider these sample table Students:

Show those students name and age, whose name has exactly 4 letters.

SELECT name, age
FROM students
WHERE name LIKE "____";

Output:

Example 2: We'd use the % wildcard to conduct a pattern match on both sides of the word "code" to find all the movies with the word "code" in the title. The SQL statement that can be used to obtain the desired results is listed below.

SELECT * FROM movies WHERE title LIKE '%code%';

Example 3: The following code block displays all records from the CUSTOMERS table where the SALARY begins with 200.

SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';

Output:

Example 4: List all products that are packaged in jars.

SELECT *
  FROM Product
 WHERE Package LIKE '%jars%'