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 :

5 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
  4. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com

    ReplyDelete
  5. Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

    ReplyDelete