SQL statement - how to 'chop' phrase

berkshirea · #1 (**permalink**) 12-17-09, 12:55

hi guys how do i do this SQL statements when a user typed in search text field in my ASP.net webpage? How do I 'chop' the typed-in phrases?

examples:

1. typed in phrase: marketing assistant jobs

the resulting SQL statement should be:

SELECT field1,field2
FROM myTable
WHERE CONTAINS(*, 'marketing AND assistant AND jobs')

2. typed in phrase: public administration

the resulting SQL would be:

SELECT field1,field2
FROM myTable
WHERE CONTAINS(*, 'public AND administration')

3. typed in phrase: SQL server full text search
result would be:

SELECT field1,field2
FROM myTable
WHERE CONTAINS(*, 'SQL AND server AND full AND text AND search')

thanks for any ideas.

.

Pat Phelan · #2 (**permalink**) 12-17-09, 13:09

This actually need sto be done before SQL Server even sees the data. Each of the .NET languages has diffent string handling functions, so there isn't a "one size fits all" solution.

I'll move this thread to the ASP forum for you. It will probably get better answers there.

-PatP

myle · #3 (**permalink**) 12-17-09, 19:17

What i Done done is join all the feilds you want to search in to make a New Feild

then use the WHERE

Whereis = "WHERE newsearchfeild Like '%" & Searchingfor & "%'"

gvee · #4 (**permalink**) 12-22-09, 08:04

It seems that replacing spaces (" ") with " AND " would give you a pretty good start...

Code:

--Initialise parameter
DECLARE @parameter varchar(100)
SET @parameter = 'marketing assistant jobs'

--Perform the replace
SET @parameter = Replace(@parameter, ' ', ' AND ')

--And then search...
SELECT field1,field2
FROM myTable
WHERE CONTAINS(*, @parameter)

berkshirea · #5 (**permalink**) 12-23-09, 05:23

Quote:

Originally Posted by gvee

It seems that replacing spaces (" ") with " AND " would give you a pretty good start...

Code:

--Initialise parameter
DECLARE @parameter varchar(100)
SET @parameter = 'marketing assistant jobs'

--Perform the replace
SET @parameter = Replace(@parameter, ' ', ' AND ')

--And then search...
SELECT field1,field2
FROM myTable
WHERE CONTAINS(*, @parameter)

hi gvee, thanks for the suggestion, it's working when you are searching words or phrases without 'AND' and 'OR' but when i have search phrases with 'AND' or 'OR' for example 'sun and moon' or 'my way or highway' it gives error. i searched to resolve this issue but i'm stuck. any ideas?

gvee · #6 (**permalink**) 12-23-09, 05:37

Why not remove/replace the "and"s from the initial string before replacing the spaces?

Code:

SET @parameter = Replace(@parameter, 'and', '&')
SET @parameter = Replace(@parameter, ' ', ' AND ')

Internet Services

Web Development

Digital Marketing

Consumer Tech