Similar to with regex in Postgresql

Question

In Postgresql database I have a column called names where I have some names which need to be parsed using regex to clean up punctuation parts. I am able to get a clean name using regexp_replace as follows:

select regexp_replace(name,'\.COM|''[A-Z]|[^a-zA-Z0-9 -]+|\s(?=&)|(?<!\w\w)(?:\s+|-)(?!\w\w)','','g') 
from tableA

However, I would like to compare with some strings that are also cleaned of punctuation. How can I use similar to with the formed regular expression?

select name
from tableA 
where (lower(name) ~ '\.COM|''[A-Za-z]|[^a-zA-Z0-9 -]+|\s(?=&)|(?<!\w\w)(?:\s+|-)(?!\w\w)') as nameParsed similar to '(fg )%' and 
      (lower(name) ~ '\.COM|''[A-Za-z]|[^a-zA-Z0-9 -]+|\s(?=&)|(?<!\w\w)(?:\s+|-)(?!\w\w)') as nameParsed similar to '%( cargo| carrier| cartage )%'

With the previous query I am getting this error:

LINE 3: ...-zA-Z0-9 -]+|\s(?=&)|(?<!\w\w)(?:\s+|-)(?!\w\w)') as namePar...

I have tried in where clause like this and it seems to be working:

select name
from tableA 
where (select lower(regexp_replace(name,'\.COM|''[A-Z]|[^a-zA-Z0-9 -]+|\s(?=&)|(?<!\w\w)(?:\s+|-)(?!\w\w)','','g'))) similar to '(fg )%'

Is this the best approach? The execution time went to 46 seconds :(

Thanks in advance

William Prigol Lopes · Accepted Answer · 2019-12-10 18:09:16Z

1

You're trying to get a column name in a WHERE clause (is a comparison, not a column). So, you can use as follows:

SELECT name 
  FROM "tableA"
 WHERE (regexp_replace(name,'\.COM|''[A-Z]|[^a-zA-Z0-9 -]+|\s(?=&)|(?<!\w\w)(?:\s+|-)(?!\w\w)','','g') similar to '(fg )%' 
    OR regexp_replace(name,'\.COM|''[A-Z]|[^a-zA-Z0-9 -]+|\s(?=&)|(?<!\w\w)(?:\s+|-)(?!\w\w)','','g') similar to '%( cargo| carrier| cartage )%');

Alternatively, you can use ilike instead of similar to if you want to find a specific word.

answered Dec 10, 2019 at 18:09

William Prigol Lopes

1,90519 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Similar to with regex in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related