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]

David Lozzi wrote:

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?

--
David Lozzi
Web Applications Developer
dlozzi@(remove-this)delphi-ts.com

"Ryan Ternier" <rt******@icompasstech.com> wrote in message
news:OF**************@TK2MSFTNGP10.phx.gbl...

David Lozzi wrote:

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