SQLite GLOB operator
MySQL LIKE operator checks whether a specific character string matches a specified pattern.
The LIKE operator does a pattern matching comparison. The operand to the right of the LIKE operator contains the pattern and the left hand operand contains the string to match against the pattern. A percent symbol ("%") in the LIKE pattern matches any sequence of zero or more characters in the string. An underscore ("_") in the LIKE pattern matches any single character in the string. Any other character matches itself or its lower/upper case equivalent (i.e. case-insensitive matching). (A bug: SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.)
Syntax
expr LIKE pat [ESCAPE 'escape_char']
- Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.
- The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
- Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator.
- LIKE operator uses WILDCARDS (i.e. %, _) to match the pattern. This is very useful to check whether a particular character or string is present in the records.
% is used to match any number of characters, even zero characters.
_ is used to match exactly one character.
To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, “\” is assumed.
\% is used to match one "%" character.
\_ Matches one "_" character
MySQL Version : 5.6
Example: MySQL LIKE operator
The following MySQL statement scans the whole author table to find any author name which have a first name starting with character ‘W’ followed by any characters.
Sample table: author
Output
PHP script
Example: MySQL LIKE operator matching to end
The following MySQL statement scans the whole author table to find any author which have the name ended with ‘on’ string.
Sample table: author
Output
Example: MySQL LIKE operator matching within the string
The following MySQL statement scans the whole author table to find any author which have a string ‘an’ in his name. Name of the author is stored in aut_name column.
Sample table: author
Output
Example: MySQL LIKE operator matching a specified string
The following MySQL statement searches all authors whose home city are such as ‘London’, ’Landon’ etc. the underscore wildcard is used to mention single character.
Sample table: author
Output
Example : MySQL LIKE operator matching escape character
To search a wildcard character or a combination of a wildcard character and any other character, the wildcard character must be preceded by an ESCAPE string. In MySQL the default ESCAPE string is "\". The following MySQL statement returns those records, whose isbn_no contain '_16'.
Sample table: book_mast
Output
Example: MySQL LIKE operator matching beginning and ending string
Wildcards can also be used in the middle of a search pattern. The following MySQL statement above will find all authors whose name begin with a ‘t’ and end with a ‘n’.
Sample table: author
Output
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics