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 :

3 comments:

  1. Hi There,


    What a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this.

    Showing error converting data type varchar to numeric.
    This code:
    declare @code as varchar(250)
    set @code = '8164,8165,8166,8167,8168'
    select * from mac_accounts where acc_code in (@code)

    Thanks a lot. This was a perfect step-by-step guide. Don’t think it could have been done better.


    Merci
    Hari

    ReplyDelete
  2. Hi Dayakar,

    Seems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on SQL Like Operator

    I have DMLStatements which contains some polish characters for POLAND country.
    When i am executing that DML through the QL Developer i am able to see my Polishcharacters.

    but when we gave the release through the SQLPLUS those characters are replacing with some special characters
    like ? (Question Mark)

    Data type of the column is : NCLOB
    NLS_CHARACTERSET : AL32UTF8
    NLS_NCHAR_CHARACTERSET : AL16UTF16

    Could you please help to resolve this?
    THANK YOU!! This saved my butt today, I’m immensely grateful.

    Kind Regards,
    Irene Hynes

    ReplyDelete
  3. Hello There,

    11/10!! Your blog is such a complete read. I like your approach with SQL Like Operator . Clearly, you wrote it to make learning a cake walk for me.

    there are TWO tables as below
    USER_DATA_B
    1>USER_ID
    2>CUST_ID
    3>CREATE_DT
    4>LOG_ID

    USER_LOGON_ID_CHANGE_B
    1> ID
    2>USER_ID
    3>NEW_LOGON_ID
    4>OLD_LOGON_ID
    5>OLD_CREATE_DT
    6>ROW_CREATE_DT

    the existing query to show the details in UI is as below

    SELECT USER_ID,OLD_CREATE_DT,ROW_CREATE_DT FROM USER_LOGON_ID_CHANGE_B WHERE OLD_LOGON_ID=?
    AND NEW_LOGON_ID IS NULL
    UNION
    SELECT USER_ID,CREATE_DT,NULL FROM USER_DATA_B WHERE LOG_ID=?
    ORDER BY OLD_CREATE_DT

    Now, my requirement is to join the above query with the following table
    CUSTOMER_ATTRIBUTE_B
    1>CUST_ID
    2> ATTRIBUTE_TYPE_CD
    3>ATTRIBUTE_DATA

    The new query output should be below
    1>USER_ID
    2>OLD_CREATE_DT,
    3>ROW_CREATE_DT
    4> ATTRIBUTE_TYPE_CD
    5>ATTRIBUTE_DATA

    Great effort, I wish I saw it earlier. Would have saved my day :)

    Thank you,
    Preethi.

    ReplyDelete