Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Regular Expressions

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Regular Expressions

      MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. In previous chapters, we have already learned about the LIKE operator for pattern matching. In this chapter, we will see another pattern matching operation based on regular expressions.

      MySQL Regular Expressions

      A regular expression is loosely defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc.
      MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multi-byte safe.
      In MySQL, it is a powerful way to perform a complex search operations in a database to retrieve desired content. And unlike the LIKE operator, the regular expressions are not restricted on search patterns (like % and _) as they use several other meta characters to expand the flexibility and control during pattern matching. This is performed using the REGEXP operator.

      Syntax

      Following is the basic syntax of the REGEXP operator in MySQL −
      expression REGEXP pattern
      

      Patterns used with REGEXP

      Following is the table of pattern, which can be used along with the REGEXP operator.
      Pattern
      What the pattern matches
      ^
      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
      [A-Z]
      Any uppercase letter
      [a-z]
      Any lowercase letter
      [0-9]
      Any digit (from 0 to 9)
      [[:<:]]
      Beginning of words
      [[:>:]]
      Ending of words
      [:class:]
      A character class, i.e. use [:alpha:] to match letters from the alphabet

      Examples

      The following example demonstrates the usage of some patterns mentioned in the table above, along with the REGEXP operator. For that, we are first creating a database table to perform the search on.
      Assume we are creating a table called CUSTOMERS using the following query −
      CREATE TABLE CUSTOMERS (
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      
      Now, insert some values into it using the INSERT statements given below −
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 ),
      (3, 'Kaushik', 23, 'Kota', 2000.00 ),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
      (7, 'Muffy', 24, 'Indore', 10000.00 );
      
      Execute the following query to display all the records present in above created table −
      SELECT * FROM CUSTOMERS;
      
      Following are the records present in CUSTOMERS table −
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00
      REGEXP with Patterns −
      Now, we are finding all the records in the CUSTOMERS table whose name starts with 'k' −
      SELECT * FROM CUSTOMERS WHERE NAME REGEXP '^k';
      
      Executing the query above will produce the following output −
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      6
      Komal
      22
      Hyderabad
      4500.00
      The following query retrieves all records in CUSTOMERS table whose name ends with 'sh' −
      SELECT * FROM CUSTOMERS WHERE NAME REGEXP 'sh$';
      
      Executing the query above will produce the following output −
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      Here, we are retrieving all the records whose name contain 'sh' −
      SELECT * FROM CUSTOMERS WHERE NAME REGEXP 'sh';
      
      As we can see the output, there are only two names that contain 'sh'.
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      3
      Kaushik
      23
      Kota
      2000.00
      In the following query, we are finding all the names starting with a vowel and ending with 'ol' −
      SELECT * FROM CUSTOMERS WHERE NAME REGEXP '^[aeiou].*ol$';
      
      It returned an empty set because the CUSTOMERS table do not have any names who starts with vowel and ends with 'ol'
      Empty set (0.00 sec)
      
      The following query finds all the names in the CUSTOMERS table whose name starts with a consonant −
      SELECT * FROM CUSTOMERS WHERE NAME REGEXP '^[^aeiou]';
      
      Executing the query above will produce the following output −
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00

      Regular Expression Functions and Operators

      Following is the table of functions and operators of regular expressions.
      S. No
      Function or Operator
      1
      NOT REGEXP
      Negation of REGEXP
      2
      REGEXP
      Checks whether the string matches regular expression or not
      3
      REGEXP_INSTR()
      Returns the starting index of substring matching regular expression
      4
      REGEXP_LIKE()
      Returns whether the string matches the regular expression
      5
      REGEXP_REPLACE()
      Replaces substrings matching the regular expression
      6
      REGEXP_SUBSTR()
      Returns substrings matching the regular expression
      7
      RLIKE
      Checks whether the string matches regular expression or not