Question about regular expressions

[juanalfonso]

Hello,

I would like to know if there is a way to say with REGEXP_SIMILAR that the string analyzed is not a certain word.

For example, I can code...

select '1' as ANSWER where REGEXP_SIMILAR('HELLO', 'HELLO') = 1

... if I want to know if the word is "HELLO".

But how could I ask for if the word is not "HELLO" not using a "not" before the regexp?

I tried something with the "[^...]" expression, but it just works for ranges or single characters like [^A-Z], [^ABC], [^A-Z123], etc. If I write [^HELLO], it is the same as if I write [^LLOHE] and that's not what I'm looking for.

Thanks and regards

PD: On this link they were asking about the same issue -> https://stackoverflow.com/questions/1395177/regex-to-exclude-a-specific-string-constant

[CarlosAL]

Hi.

I'm not sure I undertand but:

SELECT '1' as ANSWER WHERE REGEXP_SIMILAR('HOLA', 'HELLO') = 0;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

ANSWER
------
1

BTEQ -- Enter your SQL request or BTEQ command:
SELECT '1' as ANSWER WHERE REGEXP_SIMILAR('HELLO', 'HELLO') = 0;

*** Query completed. No rows found.
*** Total elapsed time was 1 second.

Cheers.

Carlos.

[juanalfonso]

Hello Carlos,

What you said would work, but I would like to say in the proper regular expression that the word can't be "HELLO" or a serie of words.

• SELECT '1' as ANSWER WHERE REGEXP_SIMILAR('GOODBYE', '[^(HELLO|JUAN|PEDRO)]') = 1;

And this doesn't do what I want. So, how could I do it?

Thanks and regards

[CarlosAL · Accepted solution]

Hi.

It seems a strange way to do it (what's wrong with old "NOT LIKE"?), but...

BTEQ -- Enter your SQL request or BTEQ command:
SELECT '1' as ANSWER WHERE REGEXP_SIMILAR('HELLO', '^((?!HOLA|PRIVET).)*') = 1;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

ANSWER
------
1

BTEQ -- Enter your SQL request or BTEQ command:
SELECT '1' as ANSWER WHERE REGEXP_SIMILAR('HOLA', '^((?!HOLA|PRIVET).)*') = 1;

*** Query completed. No rows found.
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:
SELECT '1' as ANSWER WHERE REGEXP_SIMILAR('PRIVET', '^((?!HOLA|PRIVET).)*') = 1;

*** Query completed. No rows found.
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:
SELECT '1' as ANSWER WHERE REGEXP_SIMILAR('OTRO', '^((?!HOLA|PRIVET).)*') = 1;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

ANSWER
------
1

Cheers.

Carlos.