The SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:
  • The percent sign % represents zero, one, or multiple characters
  • The underscore sign _ represents one, single character

Syntax:-

SELECT column1, column2...FROM table_name WHERE columnN LIKE pattern;

Demo Database (Customers)


CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Lulea S-958 22 Sweden

The _ Wildcard

The _ wildcard represents a single character.It can be any character or number, but each _ represents one, and only one, character.

Example:-

SELECT * FROM Customers WHERE city LIKE 'L_nd__';

The % Wildcard

The % wildcard represents any number of characters, even zero characters.

Example:-

SELECT * FROM Customers WHERE city LIKE '%L%';

Starts With

To return records that starts with a specific letter or phrase, add the % at the end of the letter or phrase.

Example:-

SELECT * FROM Customers WHERE CustomerName LIKE 'La%';

SELECT * FROM Customers WHERE CustomerName LIKE 'a%' OR CustomerName LIKE 'b%';

Ends With

To return records that ends with a specific letter or phrase, add the % at the beginning of the letter or phrase.

Example:-

SELECT * FROM Customers WHERE CustomerName LIKE '%a';

SELECT * FROM Customers WHERE CustomerName LIKE 'b%s';

Contains

To return records that contains a specific letter or phrase, add the % both before and after the letter or phrase.

Example:-

SELECT * FROM Customers WHERE CustomerName LIKE '%or%';

Combine Wildcards

Any wildcard, like % and _ , can be used in combination with other wildcards.

Example:-

SELECT * FROM Customers WHERE CustomerName LIKE 'a__%';

SELECT * FROM Customers WHERE CustomerName LIKE '_r%';

SQL Not Like

Sometimes we want to get records that doesn’t match the like pattern. In that case we can use sql not like operator. SQL not like statement syntax will be like below.

Example:-

SELECT CustomerName FROM Customer WHERE CustomerName NOT LIKE 'A%';

MySQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ALTER TABLE - ADD Column

To add a column in a table, use the following syntax:

Syntax:-

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE Customers ADD Orderamount int;

ALTER TABLE - MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:

Syntax:-

ALTER TABLE table_name MODIFY COLUMN column_name datatype;
ALTER TABLE Customers MODIFY COLUMN Orderamount int(10);

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

Syntax:-

ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE Customers DROP COLUMN ContactName ;

SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Syntax:-

SELECT DISTINCT column1, column2...FROM table_name;

SELECT DISTINCT Country FROM Customers;

Arthmetic Operation in SQL

SQL support all arthmetic operation in a table

Example:-

Update Customers set Orderamount+100 where id>=1;

MySQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.

Example:-

CREATE INDEX idx_name ON Customers (CustomerName);

CREATE INDEX idx_name ON Customers (CustomerName,ContactName);

DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

Example:-

ALTER TABLE Customers DROP INDEX CustomerName;

MySQL LIMIT Clause

The LIMIT clause is used to specify the number of records to return.

Example:-

SELECT * FROM Customers LIMIT 3;

MySQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

Example:-

SELECT * FROM Customers ORDER BY Country;

SELECT * FROM Customers ORDER BY Country DESC;

MySQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Example:-

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;