Is there another style for writing LIKE queries?

I write some queries like this:

SELECT *
FROM Sample.dbo.Temp
WHERE
   Name LIKE '%ab%'
OR Name LIKE '%fg%'
OR NAME LIKE '%asd%'
OR ...
OR ...
OR Name LIKE '%kj%'

Is there anyway I can rewrite this query like this:

SELECT *
FROM Sample.dbo.Temp
WHERE
   Name LIKE (
             '%ab%'  
          OR '%fg%'
          OR '%asd%'
          OR ...
          OR ...
          OR '%kj%'
              )

Just looks more comfortable both from a readability point of view and manageability. If the column Name changes, I can al开发者_StackOverflow中文版ways make one change instead of a hundred (or using Find and Replace). Any suggestions?

No, you have to keep repeating the LIKE

Although you could probably fool around a bit to make it work something like that, it won't be prettier or more readable.

Perhaps you should generate the query programmatically instead of manually writing this?

PS: perhaps a fulltext index is a better idea here?

You can put the values in a table, perhaps a CTE, and semijoin to your table e.g.

WITH params 
     AS 
     (
      SELECT * 
        FROM (
              VALUES ('at'),
                     ('fg'),
                     ('asd'),
                     ('kj')
             ) AS T (param)
     )
SELECT *
  FROM Sample.dbo.Temp T
 WHERE EXISTS (
               SELECT *
                 FROM params P
                WHERE T.Name LIKE '%' + P.param + '%'
              );

That looks long winded but if the CTE was instead a base table them the query could be data-driven i.e. if the list of parameter values need to change in the future then it would involve merely updating a table rather than amending hard-coded values (possibly in multiple objects).