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
Solved! Go to 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.
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.
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.
And this doesn't do what I want. So, how could I do it?
Thanks and regards
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.