REGULAR EXPRESSIONS(RegEx)

---

MySQL - RegExp

MySQL supports another type of pattern matching operation based on the regular expressions and the REGEXP operator.

• It provide a powerful and flexible pattern match that can help us implement power search utilities for our database systems.


• REGEXP is the operator used when performing regular expression pattern matches. RLIKE is the synonym.


• It also supports a number of metacharacters which allow more flexibility and control when performing pattern matching.


• The backslash is used as an escape character. It's only considered in the pattern match if double backslashes have used.


• Not case sensitive.
  
  

  Pattern	Description
  *	Zero or more instances of string preceding it
  +	One or more instances of strings preceding it
  .	Any single character
  ?	Match zero or one instances of the strings preceding it.
  ^	caret(^) matches Beginning of string
  $	End of string
  [abc]	Any character listed between the square brackets
  [^abc]	Any character not listed between the square brackets
  [A-Z]	match any upper case letter.
  [a-z]	match any lower case letter
  [0-9]	match any digit from 0 through to 9.
  [[:<:]]	matches the beginning of words.
  [[:>:]]	matches the end of words.
  [:class:]	matches a character class i.e. [:alpha:] to match letters, [:space:] to match whitespace [:punct:] is match punctuations and [:upper:] for upper class letters.
  p1/p2/p3	Alternation; matches any of the patterns p1, p2, or p3
  {n}	n instances of preceding element
  {m,n}	m through n instances of preceding element

  
  

  Examples with explanation :

  
  
  

  Match beginning of string(^):

  
  

  Gives all the names starting with 'Ja'.
  
  SELECT ParentName FROM studentrecord WHERE ParentName REGEXP '^Ja';
  
  
  ---------RESULT-----------
  
  +-------------------+
  | ParentName        | 
  +-------------------+
  | Jagannath         |
  +-------------------+
  

  
  

  Match the end of a string($):

  
  

  Gives all the names ending with 'ha'.
  
  SELECT  StudentName FROM  studentinfo WHERE StudentName REGEXP 'ha$';
  
  
  -----------RESULT------------
  
  +-------------------+
  | StudentName       |
  +-------------------+
  | Manjusha          |
  +-------------------+
  | Sweekrutha        |
  +-------------------+
  

  
  

  Match zero or one instance of the strings preceding it(?):

  
  

  Gives all the titles containing 'stu?'.
  
  SELECT studentmarks FROM Studentmarks WHERE studentmarks  REGEXP 'stu?'; 
  
  
  ----------RESULT-----------
  
  +-----------------+
  | Studentmarks    |
  +-----------------+
  

  
  

  matches any of the patterns p1, p2, or p3(p1|p2|p3):

  
  

  Gives all the names containing 'nn|ee' .
  
  
  SELECT ParentName FROM studentrecord WHERE ParentName REGEXP 'nn|ee' ;
  
  
  ---------RESULT------------
  
  +---------------+
  |ParentName     |
  +---------------+
  |Jagannath      |
  |Praveen        | 
  +---------------+
  
  

  
  

  Matches any character listed between the square brackets([abc]):

  
  

  Gives all the names containing 'm' or 'n'
  
  SELECT StudentName FROM studentinfo WHERE StudentName REGEXP '[mn]' ;
  
  
  ------------RESULT-----------
  
  +--------------+
  | StudentName  |
  +--------------+ 
  | Manjusha     |
  | Noothan      |
  | Meherish     |
  | Sahayana     |
  | manju        |
  +--------------+
  
  
  

  
  

  Matches any lower case letter between 'a' to 'z'- ([a-z]) ([a-z] and (.)):

  
  

  Retrieve all names that contain a letter in the range of 'b' and 'z', followed by any character, followed by the letter 'a'.
  
  Matches any single character(.)
  
  
  SELECT   StudentName FROM studentrecord WHERE  StudentName REGEXP '[b-z].[a]';
  
  
  ---------RESULT-----------
  
  +---------------+
  |StudentName    |
  +---------------+
  |Anasya         |
  |Shivanatini    |
  +---------------+
  
  
  
  
  

  
  
  

  MySQL TRIGGERS

  MySQL - Triggers

  posted on 2019-11-29 21:44:07 - mysql Tutorials

  
  

  Grant_ Revoke Privilege

  MySQL - Grant_ Revoke Privilege

  posted on 2019-11-26 23:15:04 - mysql Tutorials

  
  

  MySQL Vs SQL

  MySQL Vs SQL

  posted on 2019-11-25 05:02:26 - mysql Tutorials

  
  

  Prompt Examples

  ChatGPT Prompt Examples

  posted on 2023-06-21 22:37:19 - ChatGPT Tutorials

  
  

  Use Cases

  Chat GPT Key Use Cases

  posted on 2023-06-21 21:03:17 - ChatGPT Tutorials

  
  

  Prompt Frameworks

  Prompt Frameworks

  posted on 2023-06-21 19:33:06 - ChatGPT Tutorials