tencent cloud

Feedback

Regular Expression Function

Last updated: 2022-03-08 12:18:16

    This document introduces the basic syntax and examples of regular expression functions.

    CLS supports the following regular expression functions:

    FunctionSyntaxDescription
    regexp_extract_allregexp_extract_all(x, regular expression) Extracts the substrings that match a specified regular expression from a specified string and returns a collection of all matched substrings.
    regexp_extract_all(x, regular expression, n)Extracts the substrings that match a specified regular expression from a specified string and returns a collection of substrings that match the target capture group.
    regexp_extractregexp_extract(x, regular expression) Extracts and returns the first substring that matches a specified regular expression from a specified string.
    regexp_extract(x, regular expression, n)Extracts the substrings that match a specified regular expression from a specified string and returns the first substring that matches the target capture group.
    regexp_likeregexp_like(x, regular expression)Checks whether a specified string matches a specified regular expression.
    regexp_replaceregexp_replace(x, regular expression)Deletes the substrings that match a specified regular expression from a specified string and returns the substrings that are not deleted.
    regexp_replace(x, regular expression, replace string)Replaces the substrings that match a specified regular expression in a specified string and returns the new string after the replacement.
    regexp_splitregexp_split(x, regular expression)Splits a specified string into multiple substrings by using a specified regular expression and returns a collection of the substrings.

    regexp_extract_all

    The regexp_extract_all function is used to extract the substrings that match a specified regular expression from a specified string.

    Syntax

    • Extract the substrings that match a specified regular expression from a specified string and return a collection of all matched substrings.

      regexp_extract_all(x, regular expression)
      
    • Extract the substrings that match a specified regular expression from a specified string and return a collection of substrings that match the target capture group.

      regexp_extract_all(x, regular expression, n)
      

    Parameter description

    Parameter Description
    x The parameter value is of the varchar type.
    regular expression The regular expression that contains capture groups. For example, (\d)(\d)(\d) indicates three capture groups.
    n The nth capture group. n is an integer that starts from 1.

    Return value type

    Array

    Example

    Extract all numbers from the value of the http_protocol field.

    • Query and analysis statement

      * | SELECT regexp_extract_all(http_protocol, '\d+') 
      
      
    • Query and analysis result

    regexp_extract

    The regexp_extract function is used to extract the first substring that matches a specified regular expression from a specified string.

    Syntax

    • Extract and return the first substring that matches a specified regular expression from a specified string.

      regexp_extract(x, regular expression)
      
    • Extract the substrings that match a specified regular expression from a specified string and return the first substring that matches the target capture group.

      regexp_extract(x, regular expression, n)
      

    Parameter description

    Parameter Description
    x The parameter value is of the varchar type.
    regular expression The regular expression that contains capture groups. For example, (\d)(\d)(\d) indicates three capture groups.
    n The nth capture group. n is an integer that starts from 1.

    Return value type

    Varchar

    Example

    Example 1. Extract the first number from the value of the http_protocol field

    • Query and analysis statement

      * | SELECT regexp_extract_all(http_protocol, '\d+') 
      
      
    • Query and analysis result

    Example 2. Extract the file information from the value of the request_uri field and count the number of times each file is accessed

    • Query and analysis statement

      * | SELECT regexp_extract(request_uri, '.*\/(index.*)', 1) AS file, count(*) AS count GROUP BY file
      
    • Query and analysis result

    regexp_like

    The regexp_like function is used to check whether a specified string matches a specified regular expression.

    Syntax

    regexp_like (x, regular expression)
    
    

    Parameter description

    Parameter Description
    x The parameter value is of the varchar type.
    regular expression Regular expression.

    Return value type

    Boolean

    Example

    Check whether the value of the server_protocol field contains digits.

    • Query and analysis statement

      * | select regexp_like(server_protocol, '\d+')
      
    • Query and analysis result

    regexp_replace

    The regexp_replace function is used to delete or replace the substrings that match a specified regular expression in a specified string.

    Syntax

    • Delete the substrings that match a specified regular expression from a specified string and return the substrings that are not deleted.

      regexp_replace (x, regular expression)
      
      
    • Replace the substrings that match a specified regular expression in a specified string and return the new string after the replacement.

      regexp_replace (x, regular expression, replace string)
      

    Parameter description

    Parameter Description
    x The parameter value is of the varchar type.
    regular expression Regular expression.
    replace string Substring that is used to replace the matched substring.

    Return value type

    String

    Example

    Delete the version number in the value of the server_protocol field and calculate the number of requests for each communication protocol.

    • Query and analysis statement

      * | select regexp_replace(server_protocol, '.\d+') AS server_protocol, count(*) AS count GROUP BY server_protocol
      
    • Query and analysis result

    regexp_split

    The regexp_split function is used to split a specified string into multiple substrings and return a collection of the substrings.

    Syntax

    regexp_split (x, regular expression)
    
    

    Parameter description

    Parameter Description
    x The parameter value is of the varchar type.
    regular expression Regular expression.

    Return value type

    Array

    Example

    Split the value of the server_protocol field with forward slashes (/).

    • Query and analysis statement

      * | select regexp_split(server_protocol, '/')
      
    • Query and analysis result

    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support