SQL Like Pattern Match

SQL Like Pattern Case Sensitive

To identify case sensitive or insensitive data, insert a new collation to your select query.

In MySQL, the LIKE statement is used to look for records that include incomplete strings. The LIKE query matches case-insensitive records by default. The query will match both lowercase and uppercase records.

Example 1: Identify everyone with the first name in a case-insensitive way. Everything should be in lower or upper case.

SELECT FIRST_NAME, LAST_NAME 
FROM EMPLOYEES 
WHERE LOWER(FIRST_NAME) LIKE '%d%';

When using LIKE to search for incomplete strings in MySQL, the case is ignored by default.

SELECT name FROM users WHERE name LIKE 't%'

Example 2: Case sensitive example:

SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CS_AS like '%hospitalist%'

Case insensitive example

SELECT *
FROM TABLE 
WHERE Name collate SQL_Latin1_General_CP1_CI_AS like '%hospitalist%'

When you perform the collation, you will find the values. The way you are writing the LIKE piece also makes the query non-sargable.

Example 3: Search all records un colors table where name is start with “Hr”.

SELECT name FROM colors WHERE name LIKE 'Hr%';

As you can see, the output only keeps a record that match case-sensitive exactly. Mysql compares data byte-by-byte when we use BINARY. It compares data character by character without BINARY.

Example 4: Postgresql like case insensitive" is similar to the LIKE operator's performance, but ILIKE is distinct in that it is used for case-insensitive pattern matching.

In our first Postgresql instance, we'll search for names that begin with the letter "j" in the table named person data.

SELECT * FROM person_data WHERE name ILIKE 'j%';

SQL Like Pattern Regex

The LIKE and REGEXP operators vary in that the LIKE produces True only if the pattern matches the entire string. * at the end of the regex (the REGEXP equivalent of LIKE's percent), because partial matching are allowed. There are no matching modes in MySQL.

REGEXP LIKE is similar to the LIKE condition, except that REGEXP LIKE matches regular expressions instead of the simple pattern matching that LIKE does. This condition evaluates strings using the input character set's characters.

If the input string fits the pattern, the LIKE operator provides a true result. NOT LIKE would only return true if the LIKE operator returned false, and false otherwise.

Patterns:

  • ^ - Beginning of string
  • $ - End of string
  • . - Any single character
  • [...] - Any character listed between the square brackets
  • [^...] - Any character not listed between the square brackets
  • p1|p2|p3 - Alternation; matches any of the patterns p1, p2, or p3
  • * - Zero or more instances of preceding element
  • + - One or more instances of preceding element
  • {n} - n instances of preceding element
  • {m,n} - m through n instances of preceding element

Syntax:

Working syntax of LIKE and NOT LIKE is as follows:

‘String’ LIKE ‘pattern’
‘String’ NOT LIKE ‘pattern’

Example 1: Let’s do some practical examples now:

‘NewYork’ LIKE ‘NewYork’                           (TRUE)
‘NewYork’ LIKE ‘New%’                                (TRUE)
‘NewYork’ LIKE ‘_Yo_’                   (TRUE)
‘NewYork’ LIKE ‘York’              (FALSE)

The literal pattern in the instances above, without the percentage and underscore signs, signifies that the string itself is a pattern that works like an equal sign and matches input letter by letter. In the instance above, the underscore (_) looks for a single character match, whereas percentage (percent) checks for none, single, or many character matches. If you want to match a substring or a small part of a string, the pattern must include (percent) at the start and end of the substring.

Example 2: The following query retrieves the first and last names of employees with the first names Steven or Stephen (where first_name starts with Ste and finishes with en, with v or ph in between):

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

Output:

FIRST_NAME LAST_NAME
Steven King
Steven Markle
Stephen Stiles

Example 3: For instance, you can easily build a SQL pattern to locate strings containing abc, but you can't write a single SQL pattern to find strings containing any of the characters a, b, or c. String content cannot be matched based on character types such as letters or numerals. MySQL provides a different form of pattern matching operation based on regular expressions and the REGEXP operator for such tasks (or NOT REGEXP to reverse the sense of the match).  Unlike percent and _, REGEXP matching uses a separate set of pattern elements (neither of which is special in regular expressions):

SELECT name FROM metal WHERE name REGEXP 'er';

Output:

name
copper
mercury
silver

SQL Like Pattern Space

Example 1: By replacing numerous spaces between text characters with a single space, "XY ZJ QR" is returned:

REGEXREPLACE("XY ZJ   QR", "\s+", " ")

Example 2: Specifies the character field data with a single space between words standardized:

REGEXREPLACE(character_field, "\s+", " ")

SQL Pattern Matching Alphabets

MySQL supports both ordinary SQL pattern matching and extended regular expression pattern matching, akin to that used by Unix utilities like vi, grep, and sed.

You can use to match any single character and percent to match an arbitrary amount of characters in SQL pattern matching (including zero characters). SQL patterns in MySQL are case-insensitive by default. The following are some examples. When using SQL patterns, avoid using = or >. Instead, use the LIKE or NOT LIKE comparison operators.

Example 1: As you've seen, the id column has a variety of values, including alphabets, numerals, and alphanumerics. Only the rows with only alphabets must be listed from this data set.

Now look for the rows that contain not just alphabets but also any additional characters.

SELECT ID FROM #TEMP
WHERE ID LIKE '%[^a-zA-Z]%'

Example 2: To find names beginning with j:

SELECT * FROM pet WHERE name LIKE 'j%';

Output:

Name Owner Species Sex Birth Death
Jegan Harold dog f 1989-05-13 NULL
Jesika Diane dog m 1989-08-31 1995-07-29

Example 3: To find names ending with cy:

SELECT * FROM pet WHERE name LIKE '%cy';

Output:

Name Owner Species Sex Birth Death
cuty Harold cat f 1993-02-04 NULL
cinthy Harold dog f 1989-05-13 NULL

Example 4: To find names containing a w:

SELECT * FROM pet WHERE name LIKE '%w%';

Output:

Name Owner Species Sex Birth Death
Claws Gwen cat m 1994-03-17 NULL
Bowser Diane dog m 1989-08-31 1995-07-29
Whistler Gwen bird NULL 1997-12-09 NULL

SQL Pattern Matching Numbers

There is no wildcard repeat of the character class when using the LIKE operator pattern-matching in SQL for a character class, as there is with regex. To put it another way, you can't do [0-9]+ or [0-9]{1,3} (both of these would also capture 0) As a result, you must zero-pad the number before comparing it.

Example 1: It's not directly a pattern, but this expression relies in part on the LIKE operator and will work for positive integers between 1 and 999:

RIGHT('00' + myfield, 3) LIKE '[0-9][0-9][0-9]'
AND RIGHT('00' + myfield, 3) <> '000'
AND LEN(myfield) <= 3

Example 2: Write a query to display employee details in which age number starts with 2.

SELECT * FROM employee_details WHERE Age LIKE '2%';

The WHERE condition was applied to the age column, followed by the LIKE clause in the SELECT query. In the LIKE clause, we've supplied the expression value as '2', followed by the percent wildcard operator. As a result of the query, any entries with age numbers beginning with '2' followed by any other number will be included in the results.

Output:

ID Name City Salary Age
1 Priyanka Sharma Nasik 26000 20
2 Riya Sharma Mumbai 72000 28
4 Neeta Desai Nasik 39500 21

In the employee_details table, there are three records with an age number that begins with the digit '2.'

Example 3: Let's gradually start by looking for all of the character expressions that aren't letters or numbers. 'percent [a-zA-Z0-9] percent,' to put it another way. There is no need to use a comma to divide each group.

SELECT
EXAMPLE_TYPE_DESC
,THE_TEXT
FROM TEST_DATA
WHERE
THE_TEXT LIKE '%[^a-zA-Z0-9]%';

Example 4: match sql pattern with like: LIKE '%\"email\";s:[1-9]:%':

The string in the database is like this: "email";s:15:

However, it does not function to see if there are any numbers larger than 1 or if there are any numbers between s:[1-9]:

where col_name regexp '"email";s:[2-9]:|"email";s:[1-9][0-9]+:'

Regular expression syntax is ignored by the LIKE comparison operator. Only the wildcards and percent are recognized.


SQL Pattern Matching Special Characters

Checks whether a given character string matches a given pattern. Common and wildcard characters can both be used in a pattern. During pattern matching, normal characters must perfectly match the characters supplied in the character string. Wildcard characters, on the other hand, can be matched with any piece of the character string. The LIKE operator is more flexible than the = and!= string comparison operators since wildcard characters are used. If any of the arguments isn't of the character string data type, the SQL Server Database Engine transforms it to that type if permitted.

Conclusion detailed in the regex documentation:

A bracket expression is a sequence of characters enclosed in the brackets '[]'.

In most cases, it matches any single character from the list (but see below).

If the list starts with "," it matches any single character from the list (except as noted below).

When two characters in a list are divided by a "-," this is shorthand for the entire range of characters in the collating sequence between those two (inclusive). For example, "[0-9]" in ASCII represents any decimal digit.

Two ranges sharing an endpoint, such as 'a-c-e', is forbidden (!).

Ranges are highly dependent on the collating sequence, therefore portable programmes should ignore using them.

Make a literal ']' the first character in the list (after a possible ").

Make the first or final character, or the second endpoint of a range, if you want to use a literal '-'.

To transform a literal '-' into a collating element, wrap it in '[.' and']'.

All other special characters, including ", lose their unique value within a bracket expression, with the exception of these and several combinations employing '[' (see following paragraphs).

Syntax:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

All special characters mean something in regexp should be escaped specially placed in the [], so [, ], ^, -.

Example 1: Double escape -> doesn't work, group is now a [ and a \.

SELECT * FROM txt WHERE txt 
REGEXP 'ab[[\\]]+cde';

Example 2: Within the group, swapping the closing bracket with the opening one. To date, this is the strangest regex I've ever written :

SELECT * FROM txt WHERE txt 
REGEXP 'ab[][]+cde';

I will get killed by such a (totally valid!) regex in a weird nightmare, I think:

SELECT * FROM txt WHERE txt 
REGEXP 'ab[]wut?[]+cde';

SQL Pattern Matching String

Rather of matching the entire string, this function evaluates the regular expression pattern and decides if it is present within string. In other words, rather than matching, this does an include operation.

When matching against a pattern string in SQL, the LIKE and NOT LIKE operators are used instead of = and!=. Patterns can include two special metacharacters: and%, which matching any single character and any sequence of characters, along with the empty string. These characters can be used to make patterns that match a wide range of values.

Example 1: Strings that begin with a particular substring:

SELECT name FROM metal WHERE name LIKE 'be%';

Output:

Name
berium

Example 2: Strings that end with a particular substring:

SELECT name FROM metal WHERE name LIKE '%er';

Output:

Name
copper
silver

Example 3: You can match the entire string by anchoring the pattern using ^ and $:

SELECT regexp_like('1a 2b 14m', '\d+b'); -- true

Example 4: postal code starts with anything but second character is A to D:

SELECT * FROM customers 
WHERE PostalCode LIKE  '_[A-D]%'

These are the most basic SQL server pattern search options. We can combine these to create a more complex pattern search.


SQL Pattern Matching Pattern

Case insensitive or case sensitive string comparisons are available.

NOT LIKE can be used to negate a LIKE condition.

You can use AND and OR to merge LIKE and other conditions.

Syntax:

SELECT columns
  FROM table
  WHERE test_column [NOT] LIKE
   'pattern';

columns is the name of the table that contains columns, and table is the name of the table that contains columns.

The name of a column in table is test column in the search criteria.

NOT LIKE retrieving rows with values that do not meet the pattern.

Examples of % and _ Patterns

PATTERN MATCHES
'A%' Matching a string of length ≥1 that starts with the letter A and ends with the letter A. There are similarities between the letters "A," "Anonymous," and "AC/DC."
'%s' matching a string of length 1 that includes the single letter s and ends with s. A string containing spaces at the end (after the s) will not match. 's', "Victoria Falls," and "DBMSs" are all matching.
'%in%' A string of length 2 that contains in anywhere is matched. 'In', 'inch,'Pine,'linchpin, and lynchpin' are all matches.
'____' Any four-character string is matched. 'ABCD,"I am,' and 'Jp' are all compatible.
'Qua__' Any five-character string that starts with Qua is matched. Matches the words 'Quack,' 'Quaff,' and 'Quake.'
'_re_' Any four-character string with re as the second and third characters is matched. 'Tree, area, and fret' are all matches.
'_re%' Matches a three-character string that starts with any character and has the characters re as its second and third characters. 'Tree', 'area', 'fret', 'are', and 'fretful' are all matches.
'%re_' Match a string of length 3 that starts with re and finishes with any character and has re as the second and third character. 'Tree', "area", "fret", "red", and "Blood red" are all similarities.

Example 1: List the authors whose last names begin with the letters ll (el-el).

SELECT au_fname, au_lname
  FROM authors
  WHERE au_lname LIKE '__ll%';

Output:

au_fname au_lname
Hallie Hull
klee Hull
Christian Kells
Kellsey

Example 2: Three different patterns for omitting phone numbers are shown in the instance. Because single-character matches (_) are quicker than multiple-character matches(%), you should choose the first option ( percent ).

SELECT au_fname, au_lname, phone
FROM authors
WHERE phone NOT LIKE '212-___-____'
AND phone NOT LIKE '415-___-%'
AND phone NOT LIKE '303-%';

Output:

au_fname au_lname phone
Sarah Buchman 718-496-7223
kellsey 650-836-7128
Paddy O'Furniture 941-925-0752

SQL Replace Pattern String

Syntax:

REPLACE (Expression, pattern, replacement)
  • Expression: The replacement function must work on the input string value.
  • Pattern: The substring to evaluate, which also serves as a positional reference for the substitution field.
  • REPLACEment: REPLACES the provided expression's specified string or character value.

Note: The SQL REPLACE function compares values depending on the input expression's collation.

Example: The REPLACE keyword is used in the following SQL to find and replace a pattern string that matches.

SELECT REPLACE('SQL Server vNext','vNext','2017') SQL2017;

SQL Search Specific Pattern

In a WHERE clause, the LIKE operator is used to look for a certain pattern in a column. It's frequently used in a Where clause to find a certain pattern in a column. When we need to match patterns rather than equals or not equals, this operator comes in handy.

  • Using the LIKE Operator.
  • Using the % Wildcard to Perform a Simple Search.
  • Omitting Patterns Using the NOT Keyword.
  • Matching a Pattern Anywhere Using the % Wildcard Twice.
  • Finding a Pattern Match at a Specific Position.
  • Supported Wildcard Expressions in Transact SQL.
  • Combining Wildcards for Complex Patterns.

The wildcard with percentile signature (percent) specifies that one or more characters can appear in this location. The underscore character can be used to indicate that any single character can appear at the location where the single underscore wildcard character (_) is defined. By putting them within the square brackets [comma-separated list of acceptable characters], we can provide a list of characters that can be permitted for a single instance at that location.

By specifying the starting and finishing character inside range inside square brackets [starting character – ending character], we can even describe the range among which we can allow a single occurrence of the character within a particular range. Additionally, by specifying the range to be omitted inside square brackets and prefixing the range with ^ character [^], we can permit the inclusion of a single character that is not within the stated range.

Indicate a single occurrence of any character between the specified strings or characters by using the underscore (_) wildcard character.

Syntax:

SELECT column1, column2, column2, ...FROM table_name WHERE columnN LIKE pattern;

Indicate a single occurrence of any character between the given strings or characters by using the underscore (_) wildcard character.

Example 1: We'll look at an example where we only receive records from the dictionary table that fit a pattern that involves as many strings before and after the occurrence of I, with I lying in between that can be any character and define underscore in between. The query statement will be " percent i i percent " and the pattern will be " % i% ":

SELECT * FROM `dictionary` WHERE meaning LIKE "%i_i%";

Example 2: The following SQL statement selects all admins with an AdminName starting with "j":

SELECT * FROM SlightBook WHERE AdminName LIKE 'j%';

SQL Pattern Matching Substring

Substring(string from pattern for escape-character) is a three-parameter function that extracts a substring that fulfills a SQL regular expression pattern. The supplied pattern must match the complete data string, much as SIMILAR TO, else the method will fail and return null. The pattern must have two occurrences of the escape character followed by a double quotation to denote the part of the pattern that should be provided on success ("). The text that matches the pattern segment between these markers is returned.

Specify patterns using special characters

character ‘%’(percent) matches any Substring

e.g.,‘Raj%’ matches any string starting with “Raj”

e.g., ‘%Raj’ matches any string ending with “Raj”

character ‘_’(underscore) matches any single character

e.g.,(a) ‘_ _ 91’ matches with any string ending with “91”, with any twocharacters before that.

(b) ‘_ _ _ _’matches any string with exactly four characters.

Patterns are case sensitive.

Using the escape character ", special characters (percent, underscore) can be incorporated in patterns '\'(backslash).

Example: Some examples, with #" delimiting the return string:

substring('foobar' from '%#"o_b#"%' for '#')   oob
substring('foobar' from '#"o_b#"%' for '#')    NULL