Select Like in Oracle

Question

I’m developing a routine to standardize the descriptions in an Oracle database. So I’m scanning the entire database where I select the description, extract it word for word and compare it to a database. Words that are not found are presented to the user who informs the correct word for that case.

After that, as I am doing word for word, I use REPLACE to do the UPDATE at the base.

Something like that:

UPDATE tabela SET DESCRICAO = REPLACE( descricao, 'ERRO', 'ACERTO' )
WHERE descricao LIKE '%ERRO%'; 

The problem with that is that if the wrong word is part of a larger word it will be overwritten improperly.

For example: in I have several descriptions that contain the word DIVAN. And in some cases this misspelled as DIVA, your asking to replace DIVA with DIVAN, with the above method, the wrong ones will be right and the right ones have been wrong: DIVANN

And if I put something like that:

UPDATE tabela SET DESCRICAO = REPLACE( descricao, 'ERRO', 'ACERTO' )
WHERE descricao LIKE '% ERRO %'; 

It will not work if the word is immediately at the beginning or end of the description.

In Sqlserver I can use something like this:

SELECT * FROM tabela WHERE '.'  + coluna +  '.' 
LIKE '%[^a-z]parametro[^a-z]%'

Thus, even if the wrong word is part of a larger word it is not selected.

So the question. What is the equivalent of this for Oracle?

Answer 1

Use the REGEXP_LIKE passing your column and your regexp:

WHERE REGEXP_LIKE (column_name, '(^|\s)ERRO(\s|$)');