Search code examples
sqlsql-servert-sqlcasesql-like

SQL CASE LIKE with multiple choices

In my query I have following part of the code:

CASE WHEN Field1 LIKE '%choice1%' OR Field1 LIKE '%Choice1%' .... THEN 'category 1'
     WHEN Field1 LIKE '%choicea%' OR Field1 LIKE '%Choiceb%' .... THEN 'category 2'
END AS 'Cats'

I have numerous of those choices (1line about 20), (2line about 15 and son on).

Can I do something to make my code cleaner, something that I usually use IN for?

Example:

CASE WHEN Field1 IN LIKE ('%choice1'.'%choice2%',...) THEN 'category 1'
     WHEN Field1 IN LIKE ('%choicea'.'%choiceb%',...) THEN 'category 2'
END AS 'Cats'

P.S. I heard that I can use somehow those || but I can't find in google how to use them.

What do you think about this problem?

Solution

  • If those choices are already known, put them in a table and use a join:

    SELECT Field1, c.Category
FROM MainTable 
LEFT JOIN Choices c ON Field1 LIKE ('%' + c.Choice + '%')

    Update:
    If there are sub-strings inside one category, add a DISTINCT to this query.