REGEXP_INSTR / negative lookahead

Database

REGEXP_INSTR / negative lookahead

Hello:

Using REGEXP_INSTR I'm trying to find records that contain a specific string (in this case BERMUDA) and do not contain another string (in this case US)

i.e., I'm interested in this string:

'something something BERMUDA misc information HAMILTON BM'

but I don't care about this:

'something something BERMUDA misc information NEWPORT BEACH US'

 

I tried to do a negative lookahead by adding (?!US) but it doesn't seem to be working. In the example below it still returns 21, but I would expect it to return 0

SELECT
'something something BERMUDA misc information NEWPORT BEACH US' AS String
,REGEXP_INSTR(String,'BERMUDA.*(?!US)')

 

Is the syntax different or is there something else I'm missing?

 

Any help would be appreciated.

 

/Jesper

 


Accepted Solutions
Senior Apprentice

Re: REGEXP_INSTR / negative lookahead

Both positive & negative lookaround are supported, works as expected when you move the ".*" into the lookahead:

RegExp_Instr(String,'BERMUDA(?!.*US)')
1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: REGEXP_INSTR / negative lookahead

I have not yet found a complete description of the pattern-matching characters in Teradata regexp_ functions, so I don't know if (?!US) is supposed to work.  But you could do two matches, one for BERMUDA and another for (not) US.  Also I would suggest using REGEXP_Similar instead, since you don't seem to care about the actual postition of the occurrence in the string.  This might work:

SELECT
'something something BERMUDA misc information NEWPORT BEACH US' AS String
,CASE when REGEXP_SIMILAR(String,'BERMUDA') = 1 and REGEXP_SIMILAR(String,'.*BERMADA.*US)') = 0 then 1 else 0 end

For that matter, if you are using this as a filter for a query you might as well use LIKE:

SELECT ... WHERE String LIKE '%BERMUDA%' and NOT (String LIKE '%BERMUDA%US%')

Re: REGEXP_INSTR / negative lookahead

Hi,

Thanks for your reply.

We use it for more advanced pattern matching than I included in my example, so using a simple LIKE is unfortunatly not an option.

We store our patterns in a table, so if I can't get negative lookforward to work I can probably get around it by adding an additional column where I then put the "negative" pattern and evalute twice, similar to what you suggested.

 

/Jesper 

Senior Apprentice

Re: REGEXP_INSTR / negative lookahead

Both positive & negative lookaround are supported, works as expected when you move the ".*" into the lookahead:

RegExp_Instr(String,'BERMUDA(?!.*US)')

Re: REGEXP_INSTR / negative lookahead

Thanks - That works!

 

/Jesper