You are here
Home > Sql Server > LIKE Operator in sql

LIKE Operator in sql

LIKE Operator

  • We can filter character string data based on pattern matching using LIKE operator.
  • We can use LIKE operator in where clause of select, delete and update statement.
  • Syntax:
  • select column1, column2,….,columnN

    from table_name

    where column LIKE pattern;

  • The wildcards used in LIKE patterns are:
  •  
    Wildcard Meaning Example
    %(percent) Any string including an empty one ‘Kp%’ string starting with Kp
    _(underscore) A single character ‘_K’% string where second character is K
    [<character list>] A single character from a list ‘[AS]%’ string where first character is A or S
    [<character range>] A single character from a range ‘[^0-9]%’ string where first character is a digit
    [^<character list or range>] A single character that is not in list or range. ‘[^0-9]%’ string where first character is not a digit

     

  • Examples:
  •  

    Table: employees

     

    id

    firstname lastname city
    1 Anil Patel

    V.V.Nagar

    2

    Piya Shah Anand
    3 Samar Joshi

    Nadiad

    4

    Samira Patel Anand

  • Retrieve the firstname that have a city begin with ‘An’.
  • select firstname from employees where city LIKE ‘An%’;  

    Output:

    firstname

    Piya

    Samira

     

  • Retrieve firstname and lastname of employees that not start with ‘S’.
  • select firstname, lastname from employees where firstname NOT LIKE ‘S%’ AND

    lastname NOT LIKE ‘S%’;

    Output:

    firstname

    lastname
    Anil

    Patel

     

  • Retrieve all the employees that city’s second letter is ‘n’.
  • select * from employees where city LIKE ‘_n%’;

    Output: 

    id

    firstname lastname city
    2 Piya Shah

    Anand

    4

    Samira Patel

    Anand

     

  • Retrieve employees table records where firstname start with either A or S.
  • select * from employees where firstname like ‘[AS]%’;

    Output:

    id

    firstname lastname city
    1 Anil Patel

    V.V.Nagar

    3

    Samar Joshi Nadiad
    4 Samira Patel

    Anand

  • Retrieve employees table records where city second letter is not ‘a’.
  • select * from employees where city like ‘_[^a]%’;

    Output:

    id

    firstname lastname city
    1 Anil Patel

    V.V.Nagar

    2

    Piya Shah Anand
    4 Samira Patel

    Anand

     

     

  • Keep visiting my site ask2tech.com for upcoming top technical article.
  •  

    Learn more about DBA at TechNet Consultancy

Leave a Reply

Top