Sql Like Operator

The SQL LIKE Operator

In this Article describe Like Operator. The SQL LIKE clause is used to compare a value to similar values using wildcard operators. The LIKE operator is used to search for a specified pattern 'start letter and end letter and Contain letter/words' in a column. There are two wildcards used in LIKE operator.
1.(%) Percent Sign.
2.(_) Under Score Sign.
Percent sign (%) - This sign represents zero, one or multiple characters. Under Score ( _ ) - This sign represents a single number or character And use in Combination.

Syntax

SELECT ColumnName FROM TableName WHERE ColumnName LIKE pattern

We use like operator many types in Sql query.-

1.Start with letter

SELECT * FROM Tbl_Employee WHERE name LIKE 'M%'

Note - In this example those name start with 'M' letter, You can get result using like Operator

2. End with letter

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '%M'
Note - In this example those name End with 'M' letter, You can get All result those name end with 'M' letter using like Operator

3.Contain letter in word

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '%M%'
in any position. Note - In this example those name contain 'M' letter in word at any position, You can get All result those name contain 'M' letter using like Operator

4.Contain in letter in word

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '%M%'
Note - In this example those name End with 'M' letter, You can get All result thoses name end with 'M' letter using like Operator

SQL _ Wildcard

1. Start letter with ( _ )

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '_e'
Note - In this example those name start with '_' means any charter but second place character is e , You can get All result those name have second position contain 'e' .

2. End letter with (_)

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE 'ra_'
Notes- In this example use like operator with _ Wildcard search result with start with ra and end with any letter.

3. Middle word have wildcard

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE 'J_c_'
Notes-In this example use like operator with _ Wildcard. The following SQL statement selects all Employee with a City starting with "J", followed by any character, followed by "c", followed by any character. You can get this type of result easily.

SQL [charlist] Wildcard

This type of pattern use where you want to fetch some records those name start with some specific character

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '[rjm]%';
Notes - In this example use like operator with charlist. The following SQL statement selects those Employee who's name starting with "r","j" & "m"apply this pattern you can find output easily.

The following SQL statement selects all employee's name start with "r", "j", or "m"
You can use different type of like see above example -

SELECT * FROM Tbl_Employee WHERE name LIKE '[a-z]%'; Note - In this example select all employee those name consist and start with 'a' to 'z' range you can get all record. if you want the you change rang also like you give '[a-m]%' use this pattern then only find within range all records.

You can use same pattern not start with character in this following example see above-

SELECT * FROM Tbl_Employee WHERE name LIKE '[!rjm]%'; Note - In this example select all employee those name not start with "r", "j", or "m" .Using this pattern you can get all records.