Is there a way to search for a specific value in columns using wildcards for columns?

I just started using SQL server for my dissertation and I have almost 20 columns named Ingredient, Ingredient1 etc that contain an ingredient each. Now, some recipes have the same ingredient, but in different columns. Is there a way to get a table with all recipes containing a specific ingredient from the table without having to check manually throughout all columns?

This is basically what I want to do, but the last line breaks the whole script, and without it I just get the column names:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'RecipeTable' AND 
      column_name LIKE 'Ingredient%' AND
      column_name = 'Olive Oil'

I tried creating a sql query, but I have no idea what to do past getting the column names from the information schema. I also tried getting this information by parsing my table in python and using pandas to iterate through all columns, but I get NAN exceptions on the first column that that ingredient is not found. e.g. my ingredient is in columns ingredient1 and ingredient5, so my program stops at ingredient2 since it can't be found there.

CodePudding user response：

Please try the following methods to search across multiple columns.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Ingredient VARCHAR(20), Ingredient1 CHAR(2), Ingredient2 VARCHAR(20));
INSERT @tbl (Ingredient, Ingredient1, Ingredient2) VALUES
('Miami', 'FL', '33160'),
('Dallas', 'TX', '15098'),
('Los Angeles', 'CA', '45660');
-- DDL and sample data population, end

DECLARE @par_Search VARCHAR(20) = 'Dallas';

-- Method #1
-- partial comparison, case insensitive
SELECT * 
FROM @tbl AS t
WHERE EXISTS (
    SELECT 1
    FROM (VALUES 
            (t.Ingredient), --Obviously list all your columns, not just these 3
            (t.Ingredient1),
            (t.Ingredient2)) AS V(SearchString)
    WHERE V.SearchString LIKE '%'   @par_Search    '%');

-- Method #2
-- exact search, case insensitive
SELECT t.*
    , wow = (SELECT t.* FOR JSON PATH)
FROM @tbl AS t
WHERE (SELECT t.* FOR JSON PATH) LIKE '%:"'   @par_Search    '"%';

-- Method #3
-- exact search, case insensitive
SELECT t.*, x
FROM @tbl AS t
    CROSS APPLY (SELECT t.* FOR XML PATH('r'), TYPE) AS t1(x)
WHERE x.exist('/r/*[lower-case(text()[1]) = lower-case(sql:variable("@par_Search"))]') = 1;

-- Method #4
-- partial comparison, case insensitive
SELECT t.*, x
FROM @tbl AS t
    CROSS APPLY (SELECT t.* FOR XML PATH('r'), TYPE) AS t1(x)
WHERE x.exist('/r/*[contains(lower-case(text()[1]), lower-case(sql:variable("@par_Search")))]') = 1;