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.
Related Links
SQL LIKE Syntax
The basic syntax is :
SELECT column_name1, column_name2 FROM table_name
WHERE column_name LIKE 'search pattern';
Sample Database Table - Books
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | A Visual Introduction To Sql | 70 | 2008 | Administration | Nirmala |
2 | SQL Server 2008 Transact-Sql | 170 | 2010 | Database | Ramanathan |
3 | SQL Functions Reference | 205 | 2015 | Optimization | Vinoth Kumar |
4 | Complete Guide To No-SQL | 195 | 2010 | Database | Ranjani Mai |
SQL LIKE Operator Example
The following SQL SELECT statement selects the all books with a BookName starting "Sql", in the "Books" table:
SELECT * FROM Books
WHERE
BookName LIKE 'Sql%';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
2 | SQL Server 2008 Transact-Sql | 170 | 2010 | Database | Ramanathan |
3 | SQL Functions Reference | 205 | 2015 | Optimization | Vinoth Kumar |
The following SQL SELECT statement selects the all books with a BookName ending "Sql", in the "Books" table:
SELECT * FROM Books
WHERE
BookName LIKE '%Sql';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | A Visual Introduction To Sql | 70 | 2008 | Administration | Nirmala |
2 | SQL Server 2008 Transact-Sql | 170 | 2010 | Database | Ramanathan |
4 | Complete Guide To No-SQL | 195 | 2010 | Database | Ranjani Mai |
SQL LIKE Operator Example With Multiple Columns
The following SQL SELECT statement selects the all books with a BookName starting "Sql" and Relyear ending "0", in the "Books" table:
SELECT * FROM Books
WHERE
BookName LIKE 'Sql%' AND RelYear LIKE '%0';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
2 | SQL Server 2008 Transact-Sql | 170 | 2010 | Database | Ramanathan |
SQL NOT LIKE Operator Example
The SQL LIKE condition can use with the SQL NOT operator.
The following SQL SELECT statement selects the all books with a BookPrice not starting "1", in the "Books" table.
SELECT * FROM Books
WHERE
BookPrice NOT LIKE '1%';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | A Visual Introduction To Sql | 70 | 2008 | Administration | Nirmala |
3 | SQL Functions Reference | 205 | 2015 | Optimization | Vinoth Kumar |
Related Links
SQL Like and In
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')
SQL Like and Not Like
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
SQL Like Case Sensitive
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%';
SQL Like Case Statement
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.
SQL Like Concat
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.
SQL Like Date
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-%'
SQL Like Dynamic Value
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%'
SQL Like Escape Characters
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%
This time, the instance will only return one record. Because only one test value begins with 1 and includes the % literal.
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 '!';
SQL Like for Number
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.
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | MP | 4500.00 |
7 | Muffy | 24 | Indore |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
3 | kaushik | 23 | Kota | 2000.00 |
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
SQL Like Groupby
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
SQL Like Inner Join
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 + "%' ";
SQL Like Multiple Condition
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.
SQL Like Multiple Wildcards
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.
SQL Like Percent
A series of 0 (zero) or more characters is represented by the percent sign character (%).
Expression | Meaning |
---|---|
LIKE 'Kim%' | match a string that starts with Kim |
LIKE '%er' | match a string that ends with er |
LIKE '%ch%' | match a string that contains ch |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
3 | kaushik | 23 | Kota | 2000.00 |
SQL Like Question Mark
? (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.
SQL Like Range
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]%'
SQL Like Replace
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.
SQL Like Spaces
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'
SQL Like Special Character
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 the list starts with '^', it matches any single character from the list (except as noted 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';
SQL Like Square Brackets
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.
SQL Like Startwith Endwith
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.
SQL Like Subquery
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
SQL Like Underscore
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';