LIKE (SQL Server Compact)

  • Article

Determines whether a given character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must match exactly the characters specified in the character string. Wildcard characters, however, can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators. If any of the arguments are not of a character string data type, SQL Server Compact converts them to a character string data type, if possible.

Syntax

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Arguments

  • match_expression
    Any valid expression in SQL Server Compact of nchar, nvarchar, or ntext data type.

  • pattern
    The pattern to search for in match_expression. It can include the following valid SQL Server Compact wildcard characters:

    Wildcard character

    Description

    Example

    %

    Any string of zero or more characters.

    WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.

    _ (underscore)

    Any single character.

    WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean, such as Dean or Sean.

  • escape_character
    Any valid expression in SQL Server Compact of any of the data types of the character string data type category. The escape_character argument has no default and must consist of only one character.

    SELECT cvchar from talltypes0 
WHERE cvchar like 'ab' escape 7

Result Types

bit

Return Value

LIKE returns TRUE if the match_expression matches the specified pattern.

Example

The following example finds all telephone numbers that start with 9 in the Customers table.

SELECT [Company Name], [Contact Name], Phone
FROM Customers
WHERE (Phone LIKE '9%')
ORDER BY [Contact Name]

Remarks

If you are comparing an exact string (without wildcard characters), you should use = or != instead of LIKE. When using LIKE, SQL Server Compact does not add padding to your match expression, and so queries against fixed-size columns will fail. If you use = or !=, the padding is automatically added. 

You cannot query Image data type by using LIKE operator.

See Also

Reference

= (Equals) (SQL Server Compact)

!= (Not Equal To) (SQL Server Compact)