Overview
ISO/IEC 19075-1:2021 - Information technology - Guidance for the use of database language SQL - Part 1: XQuery regular expressions - defines how SQL uses the XQuery/XPath regular expression syntax (derived from Perl) inside SQL environments. The standard documents the supported regular-expression constructs, escape sequences, matching rules and modes, and the SQL operators that use this syntax. It is intended as guidance for implementing and using XQuery regular expressions in SQL (ISO/IEC 9075).
Key topics and technical requirements
- XQuery regular expression syntax adopted from XQuery and XPath Functions and Operators 3.1, adapted for SQL string normalization differences.
- Metacharacters and escape sequences: complete list of metacharacters (. \ ? * + { } ( ) | [ ] ^ $) and how to escape them; single-character and multi-character escapes (for newline, tab, etc.).
- Character classes and Unicode support: category escapes such as \p{L}, \p{Lu}, \p{Nd}, block escapes like \p{IsBasicLatin}, and complementary \P{...} forms - consistent with XML Schema Part 2 and Unicode TR#18.
- Dot, anchors, line terminators, and modes: default dot behavior (not matching line terminators), dot-all (flag 's'), multi-line mode (flag 'm'), and the definition of line terminators (noting RDBMS strings may not be XML-normalized).
- Bracket expressions, alternation, quantifiers, precedence, capture/back-references: detailed treatment of pattern grouping, repetition, negation and subtraction in character classes.
- Operators using regular expressions in SQL:
- LIKE_REGEX - predicate to test existence of a match.
- OCCURRENCES_REGEX - numeric function returning count of matches.
- POSITION_REGEX - position of a match (supports AFTER and OCCURRENCE).
- SUBSTRING_REGEX - extract substring matching a pattern.
- TRANSLATE_REGEX - perform replacements using a regular expression.
Applications and practical value
- Enables robust pattern matching, search-and-extract, counting occurrences, position searching and regex-based replacements directly in SQL queries.
- Useful for data cleaning, validation, text parsing, log analysis, ETL transformations and SQL/XML processing where Unicode-aware regular expressions are required.
- Helps DBMS vendors implement consistent XQuery-compatible regex behavior and aids developers in writing portable SQL that uses advanced regex features.
Who should use this standard
- Database engine implementers and SQL standards teams.
- SQL developers, data engineers, architects and QA testers working with SQL/XML and Unicode text processing.
- Tool vendors integrating regex capabilities into SQL clients, middleware or ETL tools.
Related standards
- ISO/IEC 9075 (SQL - multiple parts: Foundation, XML-related specs)
- XQuery and XPath Functions and Operators 3.1 (W3C)
- XML 1.0 / 1.1, XML Schema Part 2: Datatypes
- Unicode Technical Standard #18
Keywords: ISO/IEC 19075-1:2021, XQuery regular expressions, SQL regex, LIKE_REGEX, POSITION_REGEX, OCCURRENCES_REGEX, SUBSTRING_REGEX, TRANSLATE_REGEX, SQL/XML, Unicode regular expressions.
Open preview (PDF)Information technology — Guidance for the use of database language SQL — Part 1: XQuery regular expressions
