advanced code snippet search

Posted By

g8rpal on 09/30/10


Tagged

multiple cross like apply


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


Way to Check Multiple LIKE without dynamic SQL

 / Published in: SQL
 

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

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 
  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] + '%'

Report this snippet  

You need to login to post a comment.