Introduction
Quick Start
Table Design
Data Loading
Data Export
Using StarRocks
Reference
- SQL Reference
  - User Account Management
    - ALTER USER
    - CREATE ROLE
    - CREATE USER
    - DROP ROLE
    - DROP USER
    - GRANT
    - REVOKE
    - SET PASSWORD
    - SET PROPERTY
    - SHOW GRANTS
    - SHOW ROLES
  - Cluster Management
  - DDL
  - DML
  - Data Type
    - Numeric
      - BIGINT
      - LARGEINT
      - SMALLINT
      - TINYINT
      - BOOLEAN
      - DECIMAL
      - DOUBLE
      - FLOAT
      - INT
    - String
      - CHAR
      - VARCHAR
      - STRING
    - Date
      - DATE
      - DATETIME
    - Others
      - HLL
      - BITMAP
  - Auxiliary Commands
    - DESCRIBE
- Function Reference
  - Date Functions
    - convert_tz
    - curdate
    - current_timestamp
    - curtime
    - datediff
    - date_add
    - date_format
    - date_sub
    - date_trunc
    - day
    - dayname
    - dayofmonth
    - dayofweek
    - dayofyear
    - from_days
    - from_unixtime
    - hour
    - minute
    - month
    - monthname
    - now
    - second
    - str_to_date
    - timediff
    - timestampadd
    - timestampdiff
    - to_date
    - to_days
    - unix_timestamp
    - utc_timestamp
    - weekofyear
    - year
  - Geographic Functions
  - String Functions
    - append_trailing_char_if_absent
    - ascii
    - char_length
    - concat
    - concat_ws
    - ends_with
    - find_in_set
    - get_json_double
    - get_json_int
    - get_json_string
    - group_concat
    - instr
    - lcase
    - left
    - length
    - locate
    - lower
    - lpad
    - ltrim
    - money_format
    - null_or_empty
    - regexp_extract
    - regexp_replace
    - repeat
    - reverse
    - right
    - rpad
    - split
    - split_part
    - starts_with
    - strleft
    - strright
  - Aggregation Functions
    - approx_count_distinct
    - avg
    - bitmap
    - count
    - hll_union_agg
    - max
    - min
    - percentile_approx
    - stddev
    - stddev_samp
    - sum
    - variance
    - var_samp
    - any_value
  - Bitmap Functions
  - Array Functions
  - cast function
  - hash function
  - Crytographic Functions
    - md5
    - sha2
- System Variables
- Error Code
- System Limit
Administration
- Build in Docker
- Manage a cluster
- Configuration
- Scale In and Out
- Backup and Restore
- Data Recovery
  - Data Recovery
  - Metadata Recovery
- Replica
- Query Planning
- Profiling
- Monitor and Alert
- User Privilege
- Authentication
- Resource Management
FAQ
- Deployment
- Data Migration
  - Data Ingestion
  - Data Export
- SQL
- Others FAQs
Benchmark
- SSB Benchmark
- TPC-H Benchmark
Release Notes
- v2.2
- v2.1
- v2.0
- v1.19

regexp_extract

description

Syntax

VARCHAR regexp_extract(VARCHAR str, VARCHAR pattern, int pos)

This function returns the first matching substring in the target value which matches the regular expression pattern. It extracts the item in pos that matches the pattern. The pattern must completely match some parts of str so that the function can return parts needed to be matched in the pattern. If no matches are found, it will return an empty string.

example

MySQL > SELECT regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 1);
+-------------------------------------------------------------+
| regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 1) |
+-------------------------------------------------------------+
| b                                                           |
+-------------------------------------------------------------+

MySQL > SELECT regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 2);
+-------------------------------------------------------------+
| regexp_extract('AbCdE', '([[:lower:]]+)C([[:lower:]]+)', 2) |
+-------------------------------------------------------------+
| d                                                           |
+-------------------------------------------------------------+

keyword

REGEXP_EXTRACT,REGEXP,EXTRACT

In this article

regexp_extract