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

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

Juan
Tags (1)

Accepted Solutions
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.

1 ACCEPTED SOLUTION
6 REPLIES 6
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
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.

Enthusiast

Re: Question about regular expressions

I've been searching for the meaning of the parts of this regular expression you made. It works, obviously, but I don't understand the meaning of the parts.

  • '^((?!HOLA|PRIVET).)*'

What does the "?!" mean? Why the "."?

 

 Thanks and regards.

Juan
Teradata Employee

Re: Question about regular expressions

There are lots of sites with info on RegEx.

 

?! is the "negative lookahead"

The "." is needed because lookahead does not match any characters, and REGEXP_SIMILAR only returns 1 if it can match the entire string (i.e.behaves as if the pattern starts with ^ and ends with $ even if that is not explicit).

Ambassador

Re: Question about regular expressions

There are lots of online tutorials for RegEx, I like regex101 because you can paste an existing RegEx and it explans it in detail.