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

Given the following table:

N_NAME
It was the best of times, it was the worst of times
It was the best of times

Applied formula:

REGEXP_COUNT(N_NAME, 'was')

Output:

N_NAME_2
2
1

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

N_NAME
It was the best of times
It is the best of times

Applied formula:

REGEXP_INSTR(N_NAME, 'was')

Output:

N_NAME_2
4
0

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

Given the following table:

N_NAME
It
It is the best of times
Nothing happens
Love it
3456

Applied formula:

REGEXP_LIKE(N_NAME, 'it.*')

Output:

N_NAME_2
true
true
false
false
false

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

Given the following table:

N_NAME
AUTOMOBILE
AUTOMOBILE Service
MACHINERY
MACHINERY Service
BUILDING

Applied formula:

REGEXP_REPLACE(N_NAME, 'AUTO.*', 'happy')

Output:

N_NAME_2
happy
happy
MACHINERY
MACHINERY Service
BUILDING

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

Given the following table:

N_NAME
AUTOMOBILE
AUTOMOBILE Service
MACHINERY
MACHINERY Service
BUILDING

Applied formula:

REGEXP_SUBSTR(N_NAME, 'AUTO.*')

Output:

N_NAME_2
AUTOMOBILE
AUTOMOBILE Service

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