I want to check for multiple patterns in a particular column.
For one pattern I can write e.g.
SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.
My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery
e.g. (SELECT '%'+name+'%' FROM <TableName>)
Any thoughts?
HiIt might work if you move your like into Exists , like
SELECT * FROM <TableName> T1
WHERE EXISTS (SELECT * FROM T2
WHERE T1.column LIKE '%' + T2.NAME + '%'
)
NB.|||
Thanks for your solution !
|||Thanks for your solution !
However my problem won't get solved this way. Actually I am building anSSRS report and the subquery was meant to be the 'Available Values ' of a multi-valued Input parameter.
When the user selects one or more params SRS will form a string of the values like " 'val1','val2','val3'...."
In my case SRS is forming a WHERE clause like
.........
.........
WHERE colname in ('val1','val2','val3'). This would search for the exact string, whereas I would like it to only match with patterns like '%val1%','%val2%','%val3%'.
i think you need to build it as
WHERE colname like '%val1%' or colname like '%val2%' or colname like '%val3%'.
|||Thnx. Shallu.....That is correct syntax, but you need to see that the number of values is not static and all of them are stored as one parameter by ssrs.
To simplify it for you, say SSRS is providing me with a string series like " '%val2%', '%val2%' ,'%val2%' ...". Now I need to use this to do my comparison.
|||HiThere might be a better solution from SSRS comunity , you should try your question there as well. From T_SQL point of view you could try following solution:
Create 2 store procedures : one - master that recived your string of parameters , parses a string into a list of single parameters and collects whatever children return , the other one - child , that can process one parameter at a time
CREATE PROCEDURE MultipleParameterSearch_procedure
@.intString varchar ()
as
CREATE TABLE #reultset (<your columns>)
DECLARE @.PARAMETER
-- parse your string into separate parameters , one at a time
...
...
WHILE @.PARAMETER is not nul
BEGIN
-- accumulate your single parameter procedure results
INSERT INTO #reultset (<your columns>)
EXEC SingleParameterSeach_Procedure @.PARAMETER
-- get next parameter
END
SELECT * FROM #reultset
RETURN
GO
CREATE PROCEDURE SingleParameterSeach_Procedure @.Parameter VARCHAR()
AS
SELECT <your columns>FROM <Table> WHERE Column LIKE '%' + @.Parameter + '%'
RETURN
GO|||Hi
Just one last suggestion.
LIKE %val%' is a very expensive operation, as no indexes can be used to help QueryOptimiser to make a quick search. Adding multiple LIKE parameters are going to decrease performance of your query. If you are doing your Report for production , explain to the user implications and pesweid them to use a single-parameter select .
:-).NB|||Thanks for taking the pains .I'll follow this up 
Please take a look at the link below:
http://www.sommarskog.se/arrays-in-sql.html
It discusses various techniques to process lists using SQL. You can use one of those methods to generate a table that contains the individual values and then use it as source in the EXISTS sub-query.
