Sign inTry Free

RLIKE and REGEXP

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

On this page

RLIKE and REGEXP are synonyms. RLIKE is discussed below. The discussion also applies to REGEXP, as both functions work identically.

RLIKE searches a string for a regular expression pattern. It returns 1 if the pattern is found and returns 0 otherwise.

RLIKE supports the extended and advanced regular expression formats. You can set the global variable regexp_format to specify which format you want to use.

Syntax

source_string RLIKE pattern
source_string REGEXP pattern

  • source_string: A string expression, such as a column name, to be searched.

  • pattern: A string literal representing a regular expression.

Examples

The following examples demonstrate how RLIKE works when the global variable regexp_format is set to 'extended'.

Call to RLIKE

Result

SELECT 'adog' RLIKE 'o';

1

SELECT 'adog' RLIKE '.o';

1

SELECT 'dog58' RLIKE '[:digit:]';

1

SELECT '1dogbat' RLIKE '[:alpha:]';

1

SELECT 'rat' RLIKE 'b|c';

0

SELECT 'cat5mouse' RLIKE 'cat$';

0

SELECT 'dogcat2' RLIKE '^dog';

1

The following examples demonstrate how RLIKE works when the global variable regexp_format is set to 'advanced'.

Call to RLIKE

Result

SELECT 'dog' RLIKE '\\D';

1

SELECT 'dog' RLIKE '\\d';

0

SELECT '26dog' RLIKE '\\W';

0

SELECT '26dog' RLIKE '\\w';

1

SELECT 'cat dog' RLIKE '\\mdog';

1

Using RLIKE With a Table

The following example shows how to call RLIKE once per table row. This example uses extended regular expression matching. Advanced regular expression matching could be used as well.

SET GLOBAL regexp_format = 'extended';


DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE regexample_table(id INT, text_field TEXT);


INSERT INTO regexample_table VALUES (1, 'the'), (2, 'athe'), (3, 'abthe'),
(4, 'abcthe'), (5, 'thethe');


SELECT id, text_field, text_field RLIKE '..the' FROM regexample_table
ORDER BY id;
+------+------------+--------------------------+
| id   | text_field | text_field RLIKE '..the' |
+------+------------+--------------------------+
|    1 | the        |                        0 |
|    2 | athe       |                        0 |
|    3 | abthe      |                        1 |
|    4 | abcthe     |                        1 |
|    5 | thethe     |                        1 |
+------+------------+--------------------------+

Last modified: April 15, 2024

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK