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.


    PatternDescription
    *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/p3Alternation; 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


    how to use range in golang

    how to use range in golang

    posted on 2022-05-03 08:28:11 - Go lang Tutorials


    find variable type in golang

    find variable type in golang

    posted on 2022-05-03 05:24:55 - Go lang Tutorials


    for loops in golang

    for loops in golang

    posted on 2022-05-03 04:27:40 - Go lang Tutorials