Question about regular expressions

Database
Enthusiast

Question about regular expressions

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

Juan
Tags (1)
3 REPLIES
Junior Supporter

Re: Question about regular expressions

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.

Enthusiast

Re: Question about regular expressions

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

Juan
Junior Supporter

Re: Question about regular expressions

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.