Transcript:
You use the LIKE operator to retrieve rows that match a “string pattern”. In this example, the string pattern is that one and is also called the “mask”. The mask for a LIKE phrase can contain special symbols like, in this case, the percent sign. These special symbols are called “wildcards”. In this example, the LIKE phrase specifies that all rows whose “words_list” column has a value that starts with the letters AL should be included in the query results. Here, the percent sign indicates that any string of zero or more characters can follow the AL letters. So the words “all”, “along” and “also” are included in the results. This example shows the other wildcard symbol available: the underscore. This symbol matches any SINGLE character. This statement selects all rows whose “words_list” column starts with the letter A, followed by any ONE character, the letter O, and any characters after that. The words “along”, “another” and “around” are included in the result set. As you can see from this example, the mask in a LIKE phrase can contain one or both of the symbols available. Masks are not case-sensitive. This example is the same as the previous one. In contrast to the LIKE operator, the REGEXP operator allows you to create complex string patterns known as “regular expressions”. The mask for a REGEXP phrase can contain special characters and constructs. In this example, the REGEXP phrase searches for the letters AT within the “words_list” column. Since the letters can be in any position within the string, “great”, “water” and “what” are included in the results. The “caret” symbol matches the pattern to the beginning of the value being tested. In this example, the mask matches the letters HO at the beginning of “words_list”, as in “home” and “house”. The dollar sign symbol matches the pattern to the end of the value being tested. In this example, the mask matches the letters HT at the end of “words_list”, as in “thought” and “right”. The pipe symbol separates two string patterns and matches either one. This example uses the pipe character to search for either of two string patterns: ET or AV. In this case, the first pattern would match “between” and the second would match “have”, so both words are included in the result set. Square brackets are used to specify multiple values. This matches any single character listed within the brackets. In this example, the “words_list” column is searched for values that contain the letter H followed by either I or E. That includes the words “another”, “here” and “his”. This matches any single character within the given range. This example uses brackets to specify multiple values and searches for words that contain the letter R followed by any letter from A to I. This includes the words “great”, “right” and “here”. This example searches the values in the “words_list” column for a word that can have two different meanings, only by changing a letter: “world” or “would”. To do that, the mask specifies the two possible characters in the third position, R and U, within brackets. In this example, the REGEXP phrase searches the values in the “words_list” column that end with any letter, a vowel, and then the letter T. This includes the words “great” and “what”. lf you use the NOT keyword, only those rows with values that don’t match the string pattern are included in the result set. The symbols presented here, are only the most common symbols that are used in regular expressions. However, MySQL supports most of the symbols that are standard for creating regular expressions. If you’re familiar with using regular expressions in other programming languages such as PHP, you’ll find that they work similarly in MySQL. Both the LIKE and REGEXP operators provide powerful functionality for finding information in a database that cannot be found any other way. However, searches that use these operators sometimes run slowly. Indeed, most LIKE and REGEXP phrases significantly degrade performance. As a result, you should only use these operators when necessary.