Skip to content

String Operations#

REGEXP_COUNT#

Syntax

REGEXP_COUNT( <subject> , <pattern> [ , <position> , <parameters> ] )

Description

Returns the number of times that a pattern occurs in a string.

View examples

Formula Editor Example

SQL Editor Example

select regexp_count('It was the best of times, it was the worst of times', '\\bwas\\b', 1) as "result" from dual;
result
2

REGEXP_INSTR#

Syntax

REGEXP_INSTR( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <option> [ , <regexp_parameters> [ , <group_num> ] ] ] ] ] )

Description

Returns the position of the specified occurrence of the regular expression pattern in the string subject. If no match is found, returns 0. Positions are 1-based, not 0-based. For example, the position of the letter “M” in “MAN” is 1, not 0.

View examples

Formula Editor Example

SQL Editor Example

The next few examples use this data:

CREATE TABLE demo1 (id INT, string1 VARCHAR);
INSERT INTO demo1 (id, string1) VALUES
  (1, 'nevermore1, nevermore2, nevermore3.')
  ;

Next, search for a matching string. In this case, the string is “nevermore” followed by a single decimal digit, e.g. “nevermore1”:

select id, string1,
    regexp_substr(string1, 'nevermore\\d') AS "SUBSTRING",
    regexp_instr( string1, 'nevermore\\d') AS "POSITION"
  from demo1
  order by id;
ID STRING1 SUBSTRING POSITION
1 nevermore1, nevermore2, nevermore3. nevermore1 1

Search for a matching string, but starting at the 5th character in the string, rather than at the 1st character in the string:

select id, string1,
    regexp_substr(string1, 'nevermore\\d', 5) AS "SUBSTRING",
    regexp_instr( string1, 'nevermore\\d', 5) AS "POSITION"
  from demo1
  order by id;
ID STRING1 SUBSTRING POSITION
1 nevermore1, nevermore2, nevermore3. nevermore2 13

REGEXPR_LIKE#

Syntax

REGEXP_LIKE( <subject> , <pattern> [ , <parameters> ] )

Description

Returns true if the subject matches the pattern. Both expressions must be text expressions.

View examples

Formula Editor Example

SQL Editor Example

create or replace table rlike_ex(city varchar(20));
insert into rlike_ex values ('Sacramento'), ('San Francisco'), ('San Jose'), (null);
select * from rlike_ex where regexp_like(city, 'san.*');

| CITY |

select * from rlike_ex where regexp_like(city, 'san.*', 'i');
CITY
San Francisco
San Jose

REGEXPR_REPLACE#

Syntax

REGEXP_REPLACE( <subject> , <pattern> [ , <replacement> , <position> , <occurrence> , <parameters> ] )

Description

Returns the subject with the specified pattern (or all occurrences of the pattern) either removed or replaced by a replacement string. If no matches are found, returns the original subject. Parentheses (( )) and square brackets ([ ]) currently must be double-escaped to parse them as literal strings.

View examples

Formula Editor Example

SQL Editor Example

select regexp_replace('Customers - (NY)','\\(|\\)','') as customers;
CUSTOMERS
Customers - NY

REGEXPR_SUBSTR#

Syntax

REGEXP_SUBSTR( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <regex_parameters> [ , <group_num> ] ] ] ] )

Description

Returns the substring that matches a regular expression within a string. If no match is found, returns NULL. Parentheses (( )) and square brackets ([ ]) currently must be double-escaped to parse them as literal strings.

View examples

Formula Editor Example

SQL Editor Example

CREATE TABLE demo2 (id INT, string1 VARCHAR);;
INSERT INTO demo2 (id, string1) VALUES
    (2, 'It was the best of times, it was the worst of times.'),
    (3, 'In    the   string   the   extra   spaces  are   redundant.'),
    (4, 'A thespian theater is nearby.')
    ;

The example looks for:

  • the word “the”
  • followed by one or more non-word characters
  • followed by one or more word characters.

Note that “Word characters” include not only the letters a-z and A-Z, but also the underscore (“_”) and the decimal digits 0-9, but not whitespace, punctuation, etc.

select id, regexp_substr(string1, 'the\\W+\\w+') as "RESULT"
  from demo2
  order by id;
ID RESULT
2 the best
3 the string
4 NULL