SQL LIKE Operator

The SQL LIKE operator is used in a SQL WHERE clause to filter or extract records for a specified pattern using wildcards (character string matches a pattern) in a table column.

You can use SQL LIKE operator when only a fragment of a text value of a column is known to search.

The SQL LIKE operator supports only text based columns like "CHAR", "VARCHAR" or "TEXT" to match a pattern.

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

Example 1: Combine the functionality of IN and LIKE in a WHERE clause. example below illustrates the concept with 3 search criteria, the real-world example I need to apply the concept to has a couple dozen. This returns 50 rows, but requires multiple OR ... LIKE functions:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name LIKE '% sports %'
OR c.Name LIKE '% exercise %'
OR c.Name LIKE '%toy%'

What I would like to do is something like this, which doesn't work:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name IN(LIKE '% sports %', LIKE '% exercise %', LIKE '%toy%')

I could load up a cursor and loop through it, but the syntax is more cumbersome than the multiple LIKE statements, not to mention most SQL programmers are horrified at the mention of the abominable word 'cursor' for performance reasons.

Example 2: 2: compare more than one Pattern using LIKE operator

SELECT name
FROM employee
WHERE name LIKE IN ('sasho','shashi%','rags')

Data is frequently untidy and inconsistent, yet you still want to be able to identify and use it, even if values that represent the same thing are written somewhat differently and thus do not match perfectly from row to row in a table. Similarly, you may wish to exclude data from a query yet want to exclude it in numerous distinct ways in the original table. The LIKE and NOT LIKE expressions allow you to declare a list of comparable values that should be returned or excluded from the results of a query. Case is unimportant in LIKE and NOT LIKE searches.

To find a pattern in a column in SQL, use the LIKE operator in the WHERE clause. To do so, you utilise a wildcard as a placeholder for certain additional variables. With LIKE, you have two wildcard options:

LIKE accepts two wildcards, and _ which denote all characters or a single character, respectively.

The wildcard % is used to replace all potential strings in the search string at its location.

The underscore _ which can be used singularly or in multiples to represent a certain number of characters, is a wildcard for indicating the presence of a single character.

Example 1: If we wanted to see the outcome data for all the wolfhounds and wolfhound mix dogs that passed through the animal center we could write the query like this:

SELECT monthyear,
       name,
       age_upon_outcome,
       sex_upon_outcome,
       outcome_type,
       outcome_subtype,
       breed
  FROM austin_animal_center_outcomes
 WHERE animal_type = "Dog"
       AND breed LIKE "%wolfhound%"
 ORDER BY monthyear

Example 2: following query matches companies like 'Data', 'DataC' 'DataCamp', 'DataMind', and so on:

SELECT name
FROM companies
WHERE name LIKE 'Data%';

The _ wildcard will match a single character. For example, the following query matches companies like 'DataCamp', 'DataComp', and so on:

SELECT name
FROM companies
WHERE name LIKE 'DataC_mp';

The NOT LIKE operator can also be used to locate records that do not fit the pattern you specify.

NOT LIKE is similar to LIKE in that it excludes data rather than incorporating it.

For instance, if we wanted to observe all the non-brown cats that came through the center, we could construct a query like this:

Example 1:

SELECT monthyear,
  age_upon_outcome,
  sex_upon_outcome,
  outcome_type,
  outcome_subtype,
  breed,
  color
  FROM austin_animal_center_outcomes
 WHERE animal_type = "Cat"
       AND color NOT LIKE "%brown%"
 ORDER BY monthyear
 LIMIT 10

To find case sensitive or insensitive data, add a new collation to your select query.

In MySQL, the LIKE statement is used to find records with incomplete strings. By default, the LIKE query returns case-insensitive results. The query will match both lowercase and uppercase records.

Postgresql ilike case insensitive" behaves similarly to the LIKE operator, but ILIKE is distinct in that it is used for case-insensitive pattern matching.

Example 1:

-- Case sensitive example

SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

-- Case insensitive example

SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

Just keep in mind the potential for performance issues. When performing the collation, you'll have to scan the clustered index to change / find the values. The query is also non-sargable due to the manner you've written the LIKE part.

Example 2: For example, Search all records un colors table where name is start with “Gr”.

SELECT name FROM colors WHERE name LIKE 'Gr%';

As you can see, the result only includes records that fit the case-sensitive criteria exactly. Mysql compares data byte-by-byte when using BINARY. It compares data character by character without the need of BINARY.

Example 3: Find everyone with first_name contains d case insensitive manner:

Make everthing either lower or upper case

SELECT FIRST_NAME , LAST_NAME 
FROM EMPLOYEES 
WHERE LOWER(FIRST_NAME) LIKE '%d%' ; 

When searching for partial strings in MySQL with LIKE you will match case-insensitive by default.

SELECT name FROM users WHERE name LIKE 't%'

Example 4: In our first example of Postgresql, we are going to use the table named person_data and search for the name that starts with “a”.

SELECT * FROM person_data WHERE name ILIKE 'a%';

Example 1: like operator in case statement.I have the table called emp it consists empid,name,contact_num columns. What i want to do is if number starts with '9' then print 'chennai' and rest of the city should be printed accordingly. I tried but dont know how to write query to getting the result.

SELECT empid,contact,'CITY'=
CASE
WHEN contact like '9%' THEN 'Chennai'
WHEN contact like '8%' THEN 'Bangalore'
WHEN contact like '7%' THEN 'Mumbai'
WHEN contact like '6%' THEN 'Pune'
ELSE 'Oversease'
END
FROM emp;

Example 2: SQL CASE with LIKE statement:

CASE WHEN countries LIKE '%'+@selCountry+'%' THEN 'national' ELSE 'regional' END

Although, as per your original problem, I'd solve it differently, splitting the content of @selcountry int a table form and joining to it.

Example 1: Like and concat

SELECT   NAME, TOWN, EmployeeNO
FROM     EmployeeS
WHERE    NAME LIKE CONCAT('%', SUBSTR(TOWN,3,1));

Output:

+--------+----------+------------+
| NAME   | TOWN     | EmployeeNO |
+--------+----------+------------+
| Bailey | Plymouth |        112 |
+--------+----------+------------+
1 row in set (0.00 sec)

Example 2: use “LIKE concat()” in MySQL:

select Name from DemoTable where Name LIKE concat('%','o','%');

Output:

+------+
| Name |
+------+
| John |
| Bob  |
+------+

Example 3: Mysql's LIKE, it is generally used to use the Like Concat () to prevent SQL injection.

So use unnotes off the way, it will be better, not recommended to use Like's way.

like concat('%/',#{datePath,jdbcType=VARCHAR},'/%')

Concat function:

• The first argument is '%', to which the back can add certain fixed characters like /.
• The passing parameter is the second of the two parameters.
• A constant character can be put to the front of the third parameter, which is the '%' of the end.

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

+-------+---------+------------+
| Empid | Empname | date       |
+-------+---------+------------+
|     1 | Girish  | 2008-12-20 |
|     2 | Komal   | 2008-12-21 |
|     3 | vineet  | 2008-12-21 |
|     4 | Amit    | 2008-12-20 |
|     2 | Komal   | 2008-12-23 |
|     2 | Sandeep | 2008-12-24 |
|     2 | suman   | 2008-12-25 |
+-------+---------+------------+

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:-

+-------+---------+------------+
| Empid | Empname | date       |
+-------+---------+------------+
|     1 | Girish  | 2008-12-20 |
|     4 | Amit    | 2008-12-20 |
+-------+---------+------------+

Example 2: For example, a query that selects objects with dates that match 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 "%" to substitute the date field's actual day.

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

Example 3: 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 1: To construct a dynamic query, I tried using a normal SelectSQL adapter service, but it didn't work. I want LIKE '% %' to be added to the query. As a result, whatever query I pass in will be a contains query rather than a straight equal. SelectSQL creates a LIKE 'Variable' when you use like in the where clause. That is not what I require.

So I switched to try to create dynamic sql and it looks like it works yet I get an error. This is the sql statement in the DynamicSQL adapter.

SELECT [recordid]
      ,[wmservice]
      ,[servicestack]
      ,[javastackdump]
      ,[errormsg]
      ,[contact]
      ,[businessunit]
      ,[severity]
      ,[datecreated]
  FROM [PxErrorLog]
where wmservice like '%${serviceName}%'

Example 2: Stored Procedure which has the below query uses Like Operator

where i need to pass a Dynamic Variable, I tried like the one below but doesn't work.. any ideas..

SELECT Vendor_Emp_id,Vendor_Emp_Email_Address FROM HTN_EMPLOYEES
WHERE VENDOR_EMP_FULL_NAME Like QUOTE(CONCAT(CONCAT('%',Search_String),'%'));

Example 3: It creates a bunch of LIKE statements for an unknown number of values, such as the code sample below:

SELECT * 
FROM T 
WHERE T.C LIKE '%S1%' 
   or T.C LIKE '%S1%' 
   or T.C LIKE '%S2%' 
   or T.C LIKE '%S3%' 
   or T.C LIKE ...
   or T.C LIKE ...
   or T.C LIKE '%Sn%'

When pattern matching, it's crucial to know how to "Escape Characters." You can search for the literal variants instead of % or .

If you want to include certain special characters in a character string, they must be escaped (protected). Here are some guidelines for escaping characters:

• The escape character (\) must be escaped as  (\\).
• In single-quote quoted strings, the single quote (') must be escaped as (\') or (\").
• In double-quote quoted strings, the double quote (") must be escaped as (\") or ("").
• For a single character, the wild card character (_) must be escaped as ( \_).
• For numerous characters, the wild card character (%) must be escaped as (\ %).
• The tab character must be escaped as follows: (\t).
• It's necessary to escape the new line character as (\n).
• The carriage return must be escaped as follows: (\r).

In a pattern search, use the ! escape character to consider wild card characters as ordinary characters, causing the resultset to return rows containing them.

NOTE: An escape character can only be a single character. Choose a character like! or # that will not appear in your data quite often.

Syntax:

You must specifically specify an escape character following the ESCAPE clause to accomplish this:

expression LIKE pattern ESCAPE escape_character

Example 1: If you implement a text-search as LIKE-query, you usually do it like this:

SELECT * FROM T_Whatever WHERE 
SomeField LIKE CONCAT('%', @in_SearchText, '%') 

However, this causes a problem when someone enters text like "50%" or "a_b" (aside from the fact that you shouldn't have to use LIKE when fulltext-search is available).

Example 2: Instead of going to fulltext-search, you can use the LIKE-escape statement to address the problem:

SELECT * FROM T_Whatever 
WHERE SomeField LIKE 
CONCAT('%', @in_SearchText, '%') ESCAPE '\'

As a result, it now considered an ESCAPE character (\). This implies that you can now simply append \ to every character in the string you're looking for, and the results will begin to match, even if the user types a special character like % or _.

Example 3: In this example, we a table called test with the following data:

test_id	test_value
1   	10%
2	    25%
3	    100
4	    99

To get all records from the test database with the % literal in the test_value.

SELECT * FROM test
WHERE test_value LIKE '%!%%' escape '!';

Output:

test_id	test_value
1	    10%
2	    25%

The ! character is identified as an escape character in this case. In the LIKE condition, the first and last % values are interpreted as standard wildcards. Because the !% is escaped, it is interpreted as a literal % value.

Example 4: To make the above instance more specific, only return test_values that begin with 1 and include the % literal. Enter the SQL statement below:

SELECT * FROM test
WHERE test_value LIKE '1%!%%' escape '!';

Output:

test_id	test_value
1	    10%

Example 5: To compare a string containing a wildcard, such as 10%, you must tell the LIKE operator to treat the percent in 10% as a regular character.

value LIKE '%10!%%' ESCAPE '!'

The ! is an escape character in this case. The LIKE operator is instructed to consider the % in the 10% as a regular character.

Example 6: The percentage symbols at the start and end of the query are wild cards for pattern matching, but the percentage after! In the pattern search, it will be considered as a regular character and will be returned in the resultset. The goal is to get percentage values out of the table. It can also be used for _, ^, [and].

SELECT * 
FROM customers 
WHERE customer_comment LIKE ('%15!%%') ESCAPE '!';

The underscore wildcard can only match one character. It returns a table result that completely matches a single character from any location.

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 database where the SALARY begins with 200.

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

Output:

Example 3: Let's say we want to find all movies released in the year 200x, where x is a single character with any value. To accomplish this, we'd use the underscore wildcard. All of the films released in the year "200x" are listed in the script beneath.

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

Output:

movie_id	title				    director		  year_released	category_id
2		   Forgetting Sarah Marshal	Nicholas Stoller	2008		2
9		   Honey mooners			Jhon Shultz		    2005		8

In our result set, only movies with 200 follows by any character in the field year released were returned. This is because the underscore wildcard matched any single character followed by the number 200.

Example 4: For instance, if we want to get the values that include (and/or end with) our numbers, we can use the LIKE operator as follows:

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

LIKE function is not supported in the GROUP BY clause.

Example 1: Correct way to use:

SELECT x.term,
  COUNT(*)
FROM (SELECT CASE
  WHEN CHARINDEX('Fall_2009', t.column) > 0 THEN
  SUBSTRING(t.column, CHARINDEX('Fall_2009', t.column), LEN(t.column))
  WHEN CHARINDEX('Spring_2009', t.column) > 0 THEN
  SUBSTRING(t.column, CHARINDEX('Spring_2009', t.column), LEN(t.column))
   ELSE
   NULL
   END as TERM
   FROM TABLE t) x
GROUP BY x.term

Example 2:

SELECT
   EXTRACT(YEAR FROM date_created) AS yr,   -- for each year
   EXTRACT(MONTH FROM date_created) AS mth, -- & month combination 
   count(*) AS c,                                 -- count all rows 
   SUM(CASE WHEN confirmed = 1 THEN 1 ELSE 0 END) -- count only confirmed rows 
FROM users
WHERE country = 'DE'
GROUP BY yr, mth

Example 1:

Table1

Name    Year    x      y
John Smith   2010    10     12
Adam Jones   2010    8      13
John Smith   2011    7      15
Adam Jones   2011    9      14

Table2

Name               Year  z    
Smith John Smith John   2010  27
Jones Adam Jones Adam   2010  25
Smith John Smith John   2011  29
Jones Adam Jones Adam   2011  21

The names in Table2 are essentially the same, with the exception that the last name and first name have been switched, and the names have been repeated once. As a result, the names in Table 1 appear in Table 2. ("John Smith" is found in "Smith John Smith John"). I'd like to use an inner join to connect Table2's z value to Table1's other data, resulting in something like this:

Name       x     y     z
John Smith 10    12    27
Adam Jones 8     13    25

So to do that, I ran this query:

Select Table1.*, Table2.z
From Table1
Inner join Table2
On Table1.Name like "%Table2.Name%" and Table1.Year=Table2.Year

Output:

Name  Year  x  y  z

Example 2: Use LIKE operator with INNER JOIN in SQL Server example

"SELECT p.Pid,p.Title,p.Technology,p.Abstract, p.keywords,s.StudentName 
from Project p join Student s on  
p.Reg#=s.Reg#  WHERE Supervisor like  '" + TextBox1.Text + "%'  ";

Example 1: general syntax of usage of multiple like conditions in Where clause.

select column1, column2, column3
where
(column1 like '05%' 
or column1 like '06%' 
or column1 like '07%')
from my_table;

Example 2: Real-life example with multiple Like conditions:

Below example is to get records from TRAVEL-DET table (travel details table).

select country_cde, date, name, age
where
(country_cde like 'BR%' 
or country_cde like 'US%' 
or country_cde like 'CA%')
from travel_det;

I filtered records of matching with Brazil (BRA), the United States of America (USA), and Canada in the instance above (CAN).

Example 3: How many records would the following query return? (Assuming the database is configured to be case-insensitive)

SELECT * FROM User_Sales WHERE First_Name LIKE '%a%' OR Last_Name LIKE '%e%';

Output:

First_Name	Last_Name	Birth_Date	Gender	Join_Date	Total_Sales
Sophie		Lee		Jan-05-1960	F	Apr-05-2015	500
Richard		Brown		Jan-07-1975	M	Apr-05-2015	200
Jamal		Santo		Oct-08-1983	M	Apr-09-2015	350
Casey		Healy		Sep-20-1969	M	Apr-09-2015	80
Jill		Wilkes		Nov-20-1979	F	Apr-15-2015	210

Example 4: Keyword should appear in both columns when searching across multiple columns with AND and OR ( by using AND )

SELECT * FROM TABLE_NAME 
WHERE Column1 Like '%keyword%' AND Column2 LIKE '%keyword%'

Keyword should present in any columns ( by using OR )

SELECT * FROM TABLE_NAME 
WHERE Column1 Like '%keyword%' OR Column2 LIKE '%keyword%'

Same way the query can be extended to search across more than two columns.

Example 1: Using Multiple _ Wildcards in the LIKE Condition

To match on a 3-digit value that ended with '5', you would need to use the _ wildcard two times. You could modify your query as follows:

SELECT *
FROM categories
WHERE category_id LIKE '__5';

Now query will return the category_id value of 125:

category_id	category_name
125		Technology

Example 2: In this example, we want to find all car prices that include the number 9 in them.

SELECT * FROM cars
WHERE price LIKE '%9%';

Output from the cars table:

id      make        model       price
4       Dodge       Challenger  29000
6       Chevrolet   Tahoe       49000

Example 3: You can use more than one wildcard character if required. In this example I add another one to the front of my pattern.

SELECT * FROM Artist
WHERE Name LIKE '%Black%';

Result:

ArtistId    Name               
----------  -------------------
11          Black Label Society
12          Black Sabbath      
38          Banda Black Rio    
137         The Black Crowes   
169         Black Eyed Peas    

Because there are two rows with Black in their names, but not at the beginning, extra rows are returned.

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

Note: Instead of using the percent sign (percent), MS Access employs the asterisk (*) 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%'

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:

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         105 | David      | Austin    |
|         109 | Daniel     | Faviet    |
+-------------+------------+-----------+

Example 2: The following query retrieves clients whose first names begin with the letters er, such as Jenifer, Kimberly, and so on.

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:

CUST_CODE  CUST_NAME            CUST_CITY       CUST_COUNTRY
---------- -------------------- --------------- -------------------
C00015     Stuart               London          UK
C00005     Sasikant             Mumbai          India
C00006     Shilton              Torento         Canada
C00017     Srinivas             Bangalore       India
C00012     Steven               San Jose        USA
C00011     Sundariya            Chennai         India

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)

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

It can also be used 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 declare a term that can be filled by any character in its place.

When the wildcard behavior is enabled, gr?y returns items containing terms such as grey or gray.

Enter 10 in the day-of-month field and? in the day-of-week field if you want a trigger to fire on a specific day of the month (for instance, the 10th), but you don't care what day of the week it is.

A question mark indicates that any nonblank character, regardless of type, must occupy a specific location.

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.

Example 1: The query below uses [] to list customer names that begin with any character between a and l in the provided range (which includes 12 characters from the alphabet).

SELECT * 
FROM customers 
WHERE first_name LIKE '[a-l]%';  

Example 2: 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]%';

Example 3: matches all records having FName that starts with a letter from A to F from Employees Table.

SELECT * FROM Employees WHERE FName LIKE '[A-F]%'

Example 1: Simple Table Query

This instance will use the sys.databases table to discover all databases that begin with DBA and then use the REPLACE function to change the value. After you've seen the data, you can create more SQL queries to rename the database from its old name to its new one.

SET NOCOUNT ON;
SELECT 
   name                             AS [Original Value], 
   REPLACE(name, 'DBA', 'SQLAdmin') AS [New Value] 
FROM sys.databases 
WHERE name LIKE 'DBA%' 

Results:

Original Value                       New Value
---------------         		---------
DBA                                    SQLAdmin
DBA_SchemaOnly                         SQLAdmin_SchemaOnly
DBA_SchemaOnly_DBCC                    SQLAdmin_SchemaOnly_DBCC
DBA_SchemaOnly_Redgate                 SQLAdmin_SchemaOnly_Redgate
DBA_Steven                             SQLAdmin_Steven

Example 2: You'd have to use updatetext because it's a text column, which is at best painful. However, if you cast contentdetails as a varchar(max), things will get better.

update table_1
set contentdetails = replace(cast(contentdetails as varchar(max)), 'FREE', '')
where contentdetails like '%FREE%'

Furthermore, I strongly advise you to investigate converting that column from text to varchar (max). It is currently deprecated, along with ntext and image, and will be deleted from SQL Server at some point in the future.

Inside a SQL LIKE, there is no way to accomplish your goals. You would need to utilise REGEXP and [[:space:]] within your expression.

To discover one or more spaces between two words, follow these steps.

WHERE col REGEXP 'firstword[[:space:]]+secondword'

Checks whether a given character string matches a given pattern. Regular and wildcard characters can both be used in a pattern. Regular characters must match the characters supplied in the character string exactly during pattern matching. Wildcard characters, on the other hand, can be matched with any piece of the character string. The LIKE operator is more versatile than the = and!= string comparison operators since wildcard characters are used. If any of the arguments isn't of the character string data type, the SQL Server Database Engine transforms it to that type if feasible.

Syntax:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

The first problem seems to be is the ^ and $ signs.

All special characters mean something in regexp should be escaped specially placed in the [], so [, ], ^, -.

In MySQL regexes, there is a question about how to escape special characters within character groups.

Conclusion detailed in the regex documentation:

A bracket expression is a sequence of characters enclosed in the brackets '[]'. In most cases, it matches any single character from the list (but see below).

If a '-' separates two characters in a list, this is shorthand for the entire range of characters in the collating order between those two (inclusive), for example, '[0-9]' in ASCII matches any decimal digit.

Two ranges sharing an endpoint, such as 'a-c-e,' is illegal(!). Ranges are extremely collating sequence-dependent, therefore portable applications should ignore them.

Make the first character (after a possible '^') a literal ']' in the list.

Make the first or final character, or the second endpoint of a range, if you wish to add a literal '-'.

To make a literal '-' the first endpoint of a range, encapsulate it with '[.' and '.]'.

All other special characters, including '\', lose their unique value inside a bracket expression, with the exception of these and several combinations employing '[' (see following paragraphs).

Example 1: Double escape -> doesn't work, group is now a [ and a \:

SELECT * FROM txt WHERE txt 
REGEXP 'ab[[\\]]+cde';

Example 2: Inside the group, swapping the closing bracket with the opening one. To date, this is the funniest regex I've ever written.

SELECT * FROM txt WHERE txt 
REGEXP 'ab[][]+cde';

Example 3: I will get killed by such a (totally valid!) regex in a weird nightmare, I think:

SELECT * FROM txt WHERE txt 
REGEXP 'ab[]wut?[]+cde';

The SQL Server LIKE condition supports the [] wildcard (square brackets wildcard). Keep in mind that the characters enclosed in square brackets are the ones you're looking for.

Simply replace the opening square bracket '[' with square brackets '[]' in this manner.

Example 1: However, the story is different when you close the bracket, i.e.

select *
from table1 
where code like N'%[blah%]%'

In this case, it becomes a match for (any) + any of ('b','l','a','h','%') + (any). For SQL Server, you can escape characters using the ESCAPE clause.

select * from table1 where code like N'%\[blah%\]%' escape '\'

Example 2: select the data in which name is having second character either ‘e’ or ‘a’:

select * from enqdata where name like '_[ae]%'

Example 3: For example:

SELECT *
FROM employees
WHERE first_name LIKE 'Sm[iy]th';

This SQL Server LIKE condition returns all workers with a 5 character first name, where the first two characters are 'Sm' and the final two characters are 'th,' and the third character is either I or 'y.' So it would match on either 'Smith' or 'Smyth' in this situation.

Example 4:

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.

In Teradata, use LIKE to see if a string column contains, begins with, or ends with a specific character.

If you want to use the code snippets in your queries, you can replace the string literals with your table/view column name.

Starts with a string

We can use % to match a

select case when 'Kontext.tech' LIKE 'Kontext%' then 1 else 0 end

The above query returns 1 as the string literal starts with Kontext.

Ends with a string

select case when 'Kontext.tech' LIKE '%tech' then 1 else 0 end

The above query returns 1 too as the string literal does end with tech.

Example 1: The following example finds the customers whose last name starts with the letter z:

SELECT customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE 'z%'
ORDER BY
    first_name;

The following example returns the customers whose last name ends with the string er:

SELECT customer_id,
    first_name,
    last_name
FROM
    sales.customers
WHERE
    last_name LIKE '%er'
ORDER BY
    first_name;

Example 2: To check if the first and last characters are vowels, use Regular Expressions and the LIKE operator. Using REGEX, check if a name starts and ends with a vowel :

SELECT stu_name 
FROM student 
WHERE stu_name LIKE '[aeiouAEIOU]%[aeiouAEIOU]'

Here [] is used for any occurrence of a particular set of characters in brackets, and percent is used for numerous occurrences of any character.

You can only have one response from a subquery when using the LIKE operator.

The IN operator allows it to match all of the subquery's results. If you're using the LIKE command because you're using wildcards, you may need to adjust your approach.

Example 1:

SELECT * FROM Contacts
join searchvalues on first_name like myvalues

This appears to be what you're looking for; no Regex is required. It's simply a correlated subquery with the LIKE criterion rather than the general equality criterion:

SELECT b.* 
FROM tablename AS b 
WHERE EXISTS 
      ( SELECT * 
        FROM words AS w 
        WHERE b.name LIKE '%' || w.word || '%'
      ) ;

Example 2:

SELECT COUNT(*) num_owned, a.owner
        FROM dba_objects a
        WHERE a.owner NOT IN (SELECT b.owner FROM dba_objects b
        WHERE b.owner LIKE 'S%')
        GROUP BY a.owner;

Output:

NUM_OWNED OWNER
473     MDSYS
3       TSMSYS
1143    FLOWS_020100
2769    PUBLIC
8       OUTLN
575     JAVA2S
339     CTXSYS
34      HR
12      FLOWS_FILES
46      DBSNMP
668     XDB

The underscore wildcard matches any single character in a word or string to match a pattern. To match a pattern of numerous characters, you can use more than one (_) underscore character.

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

Example 1: 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:

CUST_CODE  CUST_NAME            CUST_CITY       CUST_COUNTRY
---------- -------------------- --------------- -------------------
C00007     Ramanathan           Chennai         India
C00009     Ramesh               Mumbai          India

Example 2: The underscore ('_') in LIKE refers to any single character rather than a specific regular character. As a result, '1MyUser4' is included. Furthermore, '_myUser1' and '_myUser3' are included for the same reason, not because the rows and pattern contain equivalent underscores.

USE TestDB
GO

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

Example 3: finds the tracks whose names contain: zero or more characters (%), followed by Br, followed by a character ( _), followed by wn, and followed by zero or more characters ( %).

SELECT trackid,
	name
FROM
	tracks
WHERE
	name LIKE '%Br_wn%';

Example 4: In the comparison value, replace the 'e' and 'a' with a character wildcard. The LIKE operator and the underscore wildcard (_) are used to accomplish this. Simply insert an underscore in the spot of the character for which multiple matches are permissible to use this wildcard.

SELECT * FROM product WHERE prod_name LIKE 'Gr_y Computer Case';