Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - LIKE - Matching a Pattern in a Character String

By: FYIcenter.com

(Continued from previous topic...)

What To Perform Pattern Match with the LIKE Operator?

Pattern match is a very important operation for search records base on character string columns. SQL Server 2005 offers the LIKE operator to perform pattern match operations in two formats:

target_string LIKE pattern
-- Returns TRUE 
   if the target string matches the pattern

target_string NOT LIKE pattern
-- Returns TRUE 
   if the target string does not match the pattern

Pattern match is a powerful operation. But you need to remember several rules:

  • Pattern may contain predefined wildcard characters, similar to Unix Regular Expressions. But they are not the same.
  • '%' is a wildcard character that matches any string of zero or more characters.
  • '_' is a wildcard character that matches any single character.
  • '_' is a wildcard character that matches any single character.
  • '[abc]' is a wildcard character that matches any character listed inside the brackets.
  • '[a-c]' is a wildcard character that matches any character in the range defined in the brackets.
  • '[^abc]' is a wildcard character that matches any character not listed inside the brackets.
  • '[^a-c]' is a wildcard character that matches any character not in the range defined in the brackets.

Here is a simple example of LIKE operator:

SELECT CASE WHEN 
      'FYIcenter.com' LIKE 'FYI%'
   THEN 'Pattern matched.'
   ELSE 'Pattern not matched.'
   END;
GO
Pattern matched.

(Continued on next topic...)

  1. What Is a Boolean Value?
  2. What Are Conditional Expressions?
  3. What Are Comparison Operations?
  4. How To Perform Comparison on Exact Numbers?
  5. How To Perform Comparison on Floating Point Numbers?
  6. How To Perform Comparison on Date and Time Values?
  7. How To Perform Comparison on Character Strings?
  8. What To Test Value Ranges with the BETWEEN Operator?
  9. What To Test Value Lists with the IN Operator?
  10. What To Perform Pattern Match with the LIKE Operator?
  11. How To Use Wildcard Characters in LIKE Operations?
  12. How To Test Subquery Results with the EXISTS Operator?
  13. How To Test Values Returned by a Subquery with the IN Operator?
  14. What Are Logical/Boolean Operations?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...