0

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.

Max
  • 5
  • 6

2 Answers2

0

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

Try [:digit:] for any digit.

see here

1010
  • 1,779
  • 17
  • 27
0

thank you @1010! You pushed me into right direction! These work for me:

4987  -  NNNN

SELECT field1 
FROM myTable1 
WHERE regexp_like(field1, '^[[:digit:]]{4}$');

Ivan Joe Test - LLLLZLLLZLLLL

SELECT field1 
FROM myTable1 
WHERE regexp_like(field1, '^[[:alpha:]]{4}[[:space:]][[:alpha:]]{3}[[:space:]][[:alpha:]]{4}$');
Max
  • 5
  • 6