Advanced Search

Way to Check Multiple LIKE without dynamic SQL

/ Published in: SQL
Save to your folder(s)
This uses CROSS APPLY and the fn_split() function to separate out parameters. The @vParam parameter stores your LIKE conditions, separated by commas.
By Atif-ullah Sheikh, 2010/09/16

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DECLARE @vTable TABLE(id INT, NAME VARCHAR(100))
  2. INSERT INTO @vTable
  3.     SELECT 1,'Shamas Qamar' UNION ALL
  4.     SELECT 2,'Atif' UNION ALL
  5.     SELECT 3,'Kashif' UNION ALL
  6.     SELECT 4,'Imran' 
  7.  
  8. DECLARE @vParam VARCHAR(100)
  9. -- To check the values with LIKE operator. These are comma separated.
  10. SET @vParam = 'Sha,hif'
  11.  
  12. -- Used CROSS APPLY to accomplish the task...
  13. SELECT * FROM @vTable 
  14. CROSS APPLY (SELECT [VALUE] FROM dbo.fnSplit(@vParam,',')) b
  15. WHERE NAME LIKE '%' + b.[VALUE] + '%'

URL: http://www.sqlservercentral.com/scripts/SQL+Server+2005/71199/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.