greptimedb/tests/cases/standalone/common/function/string/regex.result

-- Regular expression function tests
-- REGEXP_MATCHES function
SELECT regexp_like('hello123world', '\d+');

+------------------------------------------------+
| regexp_like(Utf8("hello123world"),Utf8("\d+")) |
+------------------------------------------------+
| true                                           |
+------------------------------------------------+

SELECT regexp_like('no numbers here', '\d+');

+--------------------------------------------------+
| regexp_like(Utf8("no numbers here"),Utf8("\d+")) |
+--------------------------------------------------+
| false                                            |
+--------------------------------------------------+

SELECT regexp_like('email@example.com', '[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z]+');

+-------------------------------------------------------------------------------------+
| regexp_like(Utf8("email@example.com"),Utf8("[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z]+")) |
+-------------------------------------------------------------------------------------+
| true                                                                                |
+-------------------------------------------------------------------------------------+

-- REGEXP_REPLACE function
SELECT REGEXP_REPLACE('hello123world', '\d+', 'XXX');

+---------------------------------------------------------------+
| regexp_replace(Utf8("hello123world"),Utf8("\d+"),Utf8("XXX")) |
+---------------------------------------------------------------+
| helloXXXworld                                                 |
+---------------------------------------------------------------+

SELECT REGEXP_REPLACE('phone: 123-456-7890', '\d{3}-\d{3}-\d{4}', 'XXX-XXX-XXXX');

+--------------------------------------------------------------------------------------------+
| regexp_replace(Utf8("phone: 123-456-7890"),Utf8("\d{3}-\d{3}-\d{4}"),Utf8("XXX-XXX-XXXX")) |
+--------------------------------------------------------------------------------------------+
| phone: XXX-XXX-XXXX                                                                        |
+--------------------------------------------------------------------------------------------+

SELECT REGEXP_REPLACE('  extra   spaces  ', '\s+', ' ');

+------------------------------------------------------------------+
| regexp_replace(Utf8("  extra   spaces  "),Utf8("\s+"),Utf8(" ")) |
+------------------------------------------------------------------+
|  extra   spaces                                                  |
+------------------------------------------------------------------+

-- REGEXP_EXTRACT function
SELECT REGEXP_EXTRACT('version 1.2.3', '\d+\.\d+\.\d+');

+-------------------------------------------------------------+
| regexp_extract(Utf8("version 1.2.3"),Utf8("\d+\.\d+\.\d+")) |
+-------------------------------------------------------------+
| 1.2.3                                                       |
+-------------------------------------------------------------+

SELECT REGEXP_EXTRACT('no match here', '\d+\.\d+\.\d+');

+-------------------------------------------------------------+
| regexp_extract(Utf8("no match here"),Utf8("\d+\.\d+\.\d+")) |
+-------------------------------------------------------------+
|                                                             |
+-------------------------------------------------------------+

-- Test with ~ operator (regex match)
SELECT 'hello123' ~ '\d+';

+--------------------------------+
| Utf8("hello123") ~ Utf8("\d+") |
+--------------------------------+
| true                           |
+--------------------------------+

SELECT 'hello world' ~ '\d+';

+-----------------------------------+
| Utf8("hello world") ~ Utf8("\d+") |
+-----------------------------------+
| false                             |
+-----------------------------------+

SELECT 'email@example.com' ~ '[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z]+';

+--------------------------------------------------------------------------+
| Utf8("email@example.com") ~ Utf8("[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z]+") |
+--------------------------------------------------------------------------+
| true                                                                     |
+--------------------------------------------------------------------------+

-- Test with table data
CREATE TABLE regex_test("text" VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO regex_test VALUES
    ('Phone: 123-456-7890', 1000),
    ('Email: user@domain.com', 2000),
    ('Version 2.1.0', 3000),
    ('No pattern here', 4000);

Affected Rows: 4

SELECT "text", REGEXP_EXTRACT("text", '\d{3}-\d{3}-\d{4}') as phone FROM regex_test ORDER BY ts;

+------------------------+--------------+
| text                   | phone        |
+------------------------+--------------+
| Phone: 123-456-7890    | 123-456-7890 |
| Email: user@domain.com |              |
| Version 2.1.0          |              |
| No pattern here        |              |
+------------------------+--------------+

SELECT "text", REGEXP_EXTRACT("text", '[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z]+') as email FROM regex_test ORDER BY ts;

+------------------------+-----------------+
| text                   | email           |
+------------------------+-----------------+
| Phone: 123-456-7890    |                 |
| Email: user@domain.com | user@domain.com |
| Version 2.1.0          |                 |
| No pattern here        |                 |
+------------------------+-----------------+

SELECT "text", REGEXP_EXTRACT("text", '\d+\.\d+\.\d+') as version FROM regex_test ORDER BY ts;

+------------------------+---------+
| text                   | version |
+------------------------+---------+
| Phone: 123-456-7890    |         |
| Email: user@domain.com |         |
| Version 2.1.0          | 2.1.0   |
| No pattern here        |         |
+------------------------+---------+

DROP TABLE regex_test;

Affected Rows: 0