I'm working on PostgreSQL and I have a table like this called words_table:
I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent id_word. In other words, I want all the words softwaredesign, software_design, software.design... and so on having the same number on its map column (in this specific case, a number from 1 to 10). The same applies to civil_engineering and IndustrialDesign.
I know this involves some regular expressions and case insensitive comparisons but i'm stuck at the SQL logic. I know these expressions could be useful:
To handle the separators
To handle the uppercase lowercase matching or
UPDATE words_table SET a.map = b.id_word WHERE word ILIKE something ... WHERE word ~* something
for case insensitive matching.
Should I create new columns with regexp_replaced words and do the mapping after that with some joins? Or maybe something with CamelCase and underscore matching? Maybe functions? Which is the optimal solution?