[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-28
8
Medium Priority
?
328 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:mousemat24
  • 5
  • 3
8 Comments
 
LVL 12

Expert Comment

by:viralypatel
ID: 37043965


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.
0
 
LVL 12

Expert Comment

by:viralypatel
ID: 37043971
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

0
 

Author Comment

by:mousemat24
ID: 37043994
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
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 12

Expert Comment

by:viralypatel
ID: 37044010
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.
0
 

Author Comment

by:mousemat24
ID: 37044017
also, all I want is a 0 or 1 i.e. if found show 1 else 0
0
 
LVL 12

Accepted Solution

by:
viralypatel earned 2000 total points
ID: 37044026
select 1 from #table
where ColContainingString like '%name=%' or ColContainingString like '%category=%'

will return 1 if a match is found. Otherwise will return blank, which u can replace with 0.
0
 

Author Comment

by:mousemat24
ID: 37044035
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
0
 
LVL 12

Expert Comment

by:viralypatel
ID: 37044070


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.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question