asked on

How can I search for multiple items (string) in a string?

Hi

How can I search for multiple items (string) in a string?

i.e.

I have this string;

'name=''fred'' color=''red'' category=''programmer'''

what I want is if there is a 'name=' OR 'category=' in the string then its valid

hope this makes sense?

thanks

viralypatel

select * from TableName
where ColContainingString like '%name=%' or ColContainingString like '%category=%'

Open in new window

TableName = your table
ColContainingString = the column in your table which has the string to search.

viralypatel

sorry, if you want both the substring to be there use "and" instead of "or"

select * from TableName
where ColContainingString like '%name=%' and ColContainingString like '%category=%'

Open in new window

mousemat24

ASKER

sorry forgot to mention, the searching is based on a string i.e.

@temp = 'name=''fred'' color=''red'' category=''programmer'''

so I need to place the outcome of the result to another variable, which I then check

viralypatel

same can be done by inserting the string @temp into a #table (temp table) and the using the select query with like clause.
assign the returning value to another variable and drop the #table.

mousemat24

ASKER

also, all I want is a 0 or 1 i.e. if found show 1 else 0

ASKER CERTIFIED SOLUTION

viralypatel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mousemat24

ASKER

is there a better way because I dont want to use temp tables to store the result of the test. I prefer to hold it in a vaiable

viralypatel

Alternative solution:

wrote this test proc:

alter procedure substringtest 
as
begin
declare @temp  varchar(100);
set @temp = 'name=''fred'' color=''red'' category=''programmer''';
print @temp

SELECT CHARINDEX('name=', @temp)
end

Open in new window

use the result of the last select statement by putting it into a variable.