H2 not recognising regexp_like

Question

I have a query written to run on an Oracle database which uses the function REGEXP_LIKE to filter some rows from the query. The specific function call is

regexp_like(col1, '[^[:alpha:]]')

The problem is when I run the query on H2 I get the following error:

org.h2.jdbc.JdbcSQLException: Function "REGEXP_LIKE" not found

If I run the query directly on the Oracle database using the SQLDeveloper tool it returns as expected.

Any ideas what could be causing this?

Hamlin

Is the problem that you need the query to run on both Oracle and H2? – Snuffer

Answer 1

See the excellent documentation.

col REGEXP '[^[:alpha:]]'

In general SQL variants either use a function or named operator.

Whether the above specific regex works I do not know. One should be able to rely on java regular expressions.

Hereinbefore

Answer 2

H2 doesn't have a function called regexp_like. But you could create one using a user defined function:

create alias regexp_like as 
$$ boolean regexpLike(String s, String p) { return s.matches(p); } $$;

drop table test;
create table test(id int, name varchar(2555));
insert into test values(1, 'Steven');
insert into test values(2, 'Stefan');
select * from test where regexp_like(name, '^Ste(v|ph)en$');

Rangy

Answer 3

REGEXP_LIKE was added to h2 as of Version 1.4.193 (2016-10-31)

http://h2database.com/html/functions.html?#regexp_like

https://github.com/h2database/h2database/pull/323

Eighteenth

checked at 1.4.200 version: [90022] REGEXP_LIKE not found – Retrospect

Its still there in the documentation and the code. Ask your own question with details if it isn't working for you. – Eighteenth

Answer 4

Here is a slightly improved version of the H2 user function by Thomas Mueller which supports flags and NULL values:

create alias regexp_like as
$$ boolean regexpLike(String s, String p, String flags) {
    if(null == s) return false;
    if(null != flags) { p = "(?" + flags + ")" + p; }
    java.util.regex.Pattern compiled = java.util.regex.Pattern.compile(p);
    return compiled.matcher(s).find();
} $$

Declarer

flags parameter seems redundant. Flags can be directly passed in the pattern by the caller. – Mourning

@Stephan: The Oracle version has three parameters, that's why I need it. – Declarer

Answer 5

https://h2database.com/html/grammar.html#regexp_predicate_right_hand_side

   Regular expression matching is used. See Java Matcher.find for details. Example: REGEXP '[a-z]'

https://overcoder.net/q/8528/%D1%80%D0%B0%D0%B7%D0%BD%D0%B8%D1%86%D0%B0-%D0%BC%D0%B5%D0%B6%D0%B4%D1%83-match-%D0%B8-find-%D0%B2-java-regex

   Matches returns true if the entire string matches the given pattern. find tries to find a substring that matches the pattern

   Matches (p) is the same as find ("^" + p + "$")

Sampl:

DELETE FROM sampledb.user_t WHERE email REGEXP '^[a-zA-Zа-яА-Я0-9 .-]+$';

Selfdetermination