Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Monday, 17 July 2017

SQL Like Operator


SQL like used to search for a pattern in given input supports with character data only.

It uses 2 special character (Meta characters).
(i)                % --->represents zero or more characters.

(ii)             – (underscore) represents one character.



Syntax for SQL Like



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


Examples

The following statement List the employees whose name begins with ‘s’


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE 's%';
             

The following statement List the employees whose name ends with ’s’


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE '%s';

The following statement List the employees whose name ends with ’S’(Capital),’s’(Small)


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE '%s' OR ename LIKE '%S';

The following statement List the employees whose name begins and ends with ‘s’


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE 's%s';


The following statement List the employees whose name got 5 letters


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE '-----';


The following statement List the employees whose name got 2nd letter as ‘i’


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE '_i%';

The following statement List the employees whose name got ‘LL’


SELECT ename, sal, job
  FROM emp
 WHERE ename LIKE '%LL%';


Searching for Meta characters

Using escape option we can search for Meta characters in given input.

List the employees whose name got ‘_‘(underscore)


SELECT ename
  FROM emp
WHERE ename LIKE '%\_%' ESCAPE '\';

Output

ANIL_KUMAR
KIRAN_KUMAR
SANDEEP_REDDY

List the employees whose name got ‘%’ symbol


SELECT ename, sal, job
  FROM emp
WHERE ename LIKE '%\%%' escape '\';

Output

ANIL%KUMAR
KIRAN%KUMAR
SANDEEP%REDDY

SQL NOT Like Operator





Syntax for SQL NOT Like



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


Examples



The following statement List the employees whose name not begins with ‘s’


SELECT ename, sal, job
  FROM emp
 WHERE ename NOT LIKE 's%';
             

The following statement List the employees whose name not ends with ’s’


SELECT ename, sal, job
  FROM emp
 WHERE ename NOT LIKE '%s';

The following statement List the employees whose name not ends with ’S’(Capital),’s’(Small)


SELECT ename, sal, job
  FROM emp
 WHERE ename NOT LIKE '%s' OR ename NOT LIKE '%S';

The following statement List the employees whose name not begins and not ends with ‘s’


SELECT ename, sal, job
  FROM emp
WHERE ename  NOT LIKE 's%s';



You May Like                                             
                            SQL IN Operator                           
                            SQL Between Operator
                            SQL is NULL AND is NOT NULL    

Share this article :

0 comments:

Post a Comment