Search using multiple keywords

[David Lozzi]

Howdy,

I am doing a simple keyword search in my SQL database from my ASP.NET w/ VB
application. Currently, I am using a PROC and within the PROC I am doing
something like so:

SELECT * FROM tblStores WHERE strName LIKE '%' + @Search + '%' AND strCity
LIKE '%' + @Search + '%' and so on

This works GREAT for a single keyword like marshalls. However, if the user
enters in 'marshalls ma' nothing returns and I understand why.

How do I split this string in the PROC and then use each piece to search
against? Is there a better way than I am thinking?

Thank you!!

David Lozzi

 

[Ryan Ternier]

Howdy,

I am doing a simple keyword search in my SQL database from my ASP.NET w/ VB
application. Currently, I am using a PROC and within the PROC I am doing
something like so:

SELECT * FROM tblStores WHERE strName LIKE '%' + @Search + '%' AND strCity
LIKE '%' + @Search + '%' and so on

This works GREAT for a single keyword like marshalls. However, if the user
enters in 'marshalls ma' nothing returns and I understand why.

How do I split this string in the PROC and then use each piece to search
against? Is there a better way than I am thinking?

Thank you!!

David Lozzi

Your code must separate the words into a CSV string.

Easy example:

mySearchString = mySearchString.Replace(" ","%,%")

then:

SELECT *
FROM tblStores
WHERE strName IN@Search
UNION
SELECT *
FROM tblStores
WHERE strCity IN @Search

That way you can get results if the search is in one clumn and not the
other.

/RT

 

[David Lozzi]

This kind of works. For example:

SELECT * FROM tblStores WHERE strState IN ('ma','nh')

works, but if I do this

SELECT * FROM tblStores WHERE strState IN ('%m%','%n%')

Nothing returns. It doesn't appear to suppose wild cards?