censoring database searches? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

censoring database searches?

Author

Topic

sengoku
Starting Member

29 Posts

Posted - 2004-07-20 : 11:46:42

hi there

i had a strange query.

at the moment, i have a 'search' function on a forum database, which obviously does something like

select * from messages where message_text like '%**SEARCH STRING**%'

that works fine

BUT, i have a small problem.

some of the posts contain codes inside [ ] brackets, and i don't want to make searching include the text inside the brackets

so, at the moment, say i have a message_text like this

hello this is a search text [hithisisacode] and this is more search text

searching for 'search text' will turn this result up, obviously
so will searching for 'thisisacode'

what i'd like to do is make it so anything inside [] brackets is completely ignored from the LIKE clause.

so, something like this ideally

select * from messages where REMOVECODES(message_text) like '%**SEARCH STRING**%'

is there any way of doing this?

thanks in advance :)

sengoku
Starting Member

29 Posts

Posted - 2004-07-20 : 11:54:08

ps i realise that i can almost do it like this

select * from messages where message_text like '%**searchstring**%' and message_text not like '[%**searchstring**%]'

but that will also filter out things like this

[hello] this is a test [hello]

searching for 'this is a test' on the above text won't show anything up.

if it's helpful, there is NEVER a space inside the [] that i want to trim out...?

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 12:20:32

I've done this in the past to, for example, remove peoples EMail addresses from postings on a forum (where people had to pay to be given contact info).

We used a mass of REPLACE commands with PATINDEX and the like to match likely EMail addresses.

But it is very hard work in SQL ... and much more easily done in the application. I would be tempted to store message_text twice - one copy with all the [CODES] removed, and do the search on that.

If the number of [CODES] is small, and with fixed text, you could do:
[code]
SELECT REPLACE(REPLACE(REPLACE(
message_text,
'[CODE1], ''),
'[CODE2], ''),
'[CODE3], '')
FROM messages
[code]
but if you make this part of your "WHERE ThisReplacedThing LIKE '%** searhc string **%' it will be awfully slow - every row has to have the REPLACE done before the LIKE can begin ...

... so again, better to clean up the text with seom UPDATE / REPLACE statements into a separate column, or table, and then search on that.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources