Search code examples
sqloracle-databaseunicoderegexp-like

Oracle SQL regexp_like by unicode types

Let's say I have a pattern in unicode types (or Unicode Categories, according to https://www.regular-expressions.info/unicode.html)

LLLLZLLLZLLLL
NNNN

I'm trying to write a sql query, selecting entries in the column which corresponds exactly to the pattern.

I do have entry "2343" in this column, along with many other mixed Letter-Number, or longer (more than 4) Number unicode characters.

I want to retrieve exactly "2343", (or any 4 unicode numbers entry).

Tried a few, for example

SELECT field1 
FROM myTable1 
WHERE regexp_like(field1, '[^\p{N}{4}S]');

SELECT field1 
FROM myTable1 
WHERE regexp_like(field1, '[^\p{N}\p{N}\p{N}\p{N}S]');

Nothing works.

Solution

  • It seems that's not the oracle syntax for unicode character classes.

    Try [:digit:] for any digit.

    see here