Searching a certain value in ANY column in a table has never been easier in Snowflake.

Imagine you have an order number, and you would like to search for this order number in a whole bunch of tables that you do not know well.

If you knew which column to search, this would be easy:

Simply SELECT * FROM DB.SCHEMA.TABLE WHERE INCONSPICUOUS_COLUMN = ‘ordernumber’

However! If only life would be so easy! You do not know which column you need to search!

Ideally you would like to search ALL columns in the entire table at once!

This is where the function SEARCH in Snowflake comes in handy.

It works like below:

SELECT
*
FROM DB.SCHEMA.TABLE
WHERE SEARCH(DB.SCHEMA.TABLE.*,		-- Using a wildcard (*) in order to search all the columns from TABLE
		‘ordernumber’)

This will search all columns from the table for the order number that you would like to inspect.

One thing to keep in mind is the following: when you provide the function SEARCH with a list of values like ‘blue green red’ or ‘blue-green-red’, the function will start searching for values ‘blue’, ‘green’ and ‘red’. With other words, spaces and dashes (among other characters) are used as delimiters.

If you wish to search for an exact match, you can use the optional argument ANALYZER, like below.

SELECT
*
FROM DB.SCHEMA.TABLE
WHERE SEARCH(DB.SCHEMA.TABLE.*,
		‘ordernumber’,
ANALYZER => ‘NO_OP_ANALYZER’)