How to search multiple strings or chars in a field in sql

Question

I am using sql server and i have a table which have two fields

Name
Description

I want to select all those rows which contains following strings

('$','%' , 'ac cd' , 'hjd oih')

I am using it like this

select Name , description from table_name 
where Description like any (' %$%',' %[%]%','%ac cd%' , '%hjd oih%')

now its giving error when i run this

Incorrect syntax near the keyword 'any'.

thanks ,

Please check similar question: – bprasanna Mar 21 '13 at 07:22 — bprasanna, Mar 21 '13 at 07:22

score 5 · Accepted Answer · edited May 23 '17 at 12:14

5

If you have multiple patterns to search then its better to create a derived table or temporary table to store the patterns

DECLARE @patterns TABLE  (
pattern VARCHAR(20)
);

INSERT INTO @patterns VALUES ('%$%'), ('%[%]%'), ('%ac cd%');

SELECT a.* FROM table_name a JOIN @patterns p ON (a.Description LIKE p.pattern);

If you have multiple columns to compare just add expression in the ON clause

SELECT a.* FROM table_name a JOIN @patterns p ON (a.Description LIKE p.pattern)
OR (a.Column2 like p.pattern);

Updated: Using View

CREATE VIEW PatternSearch
AS
 WITH Patterns (Patterns)
  AS
  (
     Select  '%Tool%'
     union all
     Select '%Sale%'
     union all
     Select  '%ac cd%'
  )
   SELECT a.* FROM table_name a JOIN Patterns p ON (a.Description LIKE p.pattern)

GO

Not tested but you can do like this

Source

edited May 23 '17 at 12:14

Community

1
1

answered Mar 21 '13 at 07:23

praveen

12,083
1
41
49

but what if want to do the same thing in a sql view . because i tried but its giving error – user2193861 Mar 21 '13 at 07:36
If you are doing it in View then i think you need to use CTE – praveen Mar 21 '13 at 07:38
This one uses UNION instead of JOIN. (I like the JOIN better...) http://stackoverflow.com/questions/1387612/how-can-i-introduce-multiple-conditions-in-like-operator?lq=1 – John Baughman Jan 22 '14 at 18:08

How to search multiple strings or chars in a field in sql

1 Answers1

Linked