Skip to content
Advertisement

Can a Wildcard Search Be Performed Amongst Fields

Always grateful for the infinite wisdom from everyone on this site.

I deal with some massive databases that are often difficult to search in. One of which is a lead database with over 1.4k columns and 3 million records.

Sometimes, I just want to query to see if a certain value exists anywhere in the database within ANY field. (Like a ctrl+F in Excel) Is it possible to perform a query that searches for a particular string or integer in all records and within any field in the database? I assume this would take a lot of computing power but it would save me a lot of time instead of trying to search for the correct field I need to be using as there are multiple fields with similar names and no concrete ER Diagram

Advertisement

Answer

Below example shows how to do so

with your_table as (
  select 1 x, 'abc' y, 'table1' z union all 
  select 2, 'xyz', 'desk3'    
)
select format('%t', t) record,
  contains_substr(t, 'xyz') has_xyz,
  contains_substr(t, '1') has_1,
  contains_substr(t, '3') has_3,
from your_table t    

with output

enter image description here

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement