SQL statement - 'chop' phrase - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL statement - 'chop' phrase

Author

Topic

ramon6969
Starting Member

11 Posts

Posted - 2009-12-17 : 11:48:31

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.

.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-17 : 12:18:36

Here's one way...replace the space with a dummy char..and then replace it with ' AND '

declare @d varchar(500) 
select @d = 'marketing assistant jobs'
select @d = replace(replace(@d,' ',char(10)),char(10),' AND ')
select @d

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-18 : 00:59:56

or

declare @d varchar(500)
select @d = 'marketing assistant jobs'
select @d = replace(@d,' ',' AND ')
select @d

Madhivanan

Failing to plan is Planning to fail

ramon6969
Starting Member

11 Posts

Posted - 2009-12-18 : 06:10:36

hi guys, thanks for your ideas.

i tried all sorts to somehow incorporate your codes to this code:

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

and i can not make it wor work when adapting your codes. how do i do that?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-18 : 07:04:44

How are you passing the value?

Madhivanan

Failing to plan is Planning to fail

ramon6969
Starting Member

11 Posts

Posted - 2009-12-22 : 06:27:50

Hi madhivanan, thanks.

It seems to be doing it except when I tried to input phrases with OR and AND. It's giving errors.

e.g.
head and shoulder
father and son
chicken or egg

example:
SELECT field1,field2
FROM myTable
WHERE CONTAINS(*, 'head AND and AND shoulder')

I search the net but cant find any clue to resolve this.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 09:37:20

try the method suggested @ 12/17/2009 : 12:18:36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources