I want to perform a search in several columns of a table. I use the following query:
select * from Tabela t inner join "TabelaPai" tp on tp."ID" = t."RefTabelaPai" and tp."RefProject" = 'projectid' where not t."Deleted" and (t.Col1 ~ '.*__param1__.*' or t.Col2 ~ '.*__param1__.*' or t.Col3 ~ '.*__param1__.*' or t.Col4 ~ '.*__param1__.*' or t.Col5 ~ '.*__param1__.*' or t.Col6 ~ '.*__param1__.*' or t.Col7 ~ '.*__param1__.*' or t.Col8 ~ '.*__param1__.*' or t.Col9 ~ '.*__param1__.*');
This will search for the keyword
__param1__ in any of the columns and it's working fine.
But I don't like the way the query looks like. Any suggestion on how to refactor the query so it can look 'prettier' (without those
~ '.*__param1__.*' repetitions, for example)?
Edit: A little of context about the query:
What leads to this usage is that I can parameterize the data in the table. For example, I have a column in a table where scripts are saved. My application allows the users to parametrize the script using something like
__param1__. If the user wants to rename the parameter I'll have to search for the usage of the parameter in every column that is parameterizable, and this is the query that finds where the parameter is used.