Prev: Case Expression Performance Implication
Next: Send Messages to Broker in Set?
From: vncntj on 22 Jul 2010 16:16
Is a query like this possible? I'm trying to filter out for words
that exist in a filter table. I'd like to filter for foul language,
but for example:

"Cat" is in the filter table.
It should also filter:


Recat
Cat!
Catyou


SELECT
id
,name
,comment
,fk_id
FROM tresponsecomments
WHERE comment NOT LIKE '%(Select filtered from tfilteredWords)%'
ORDER BY id DESC
LIMIT 0 , 30
From: Erland Sommarskog on 22 Jul 2010 16:51
vncntj (vincent.s.jones(a)gmail.com) writes:
> Is a query like this possible? I'm trying to filter out for words
> that exist in a filter table. I'd like to filter for foul language,
> but for example:
>...
> SELECT
> id
> ,name
> ,comment
> ,fk_id
> FROM tresponsecomments
> WHERE comment NOT LIKE '%(Select filtered from tfilteredWords)%'
> ORDER BY id DESC
> LIMIT 0 , 30

You need to write it this way:

SELECT
id
,name
,comment
,fk_id
FROM tresponsecomments
WHERE comment NOT LIKE '%' + (Select filtered from tfilteredWords) + '%'
ORDER BY id DESC
LIMIT 0 , 30

With the caveat that your syntax appears to be for MySQL, and this
newsgroup is for Microsoft SQL Server. (There is not LIMIT clause in
SQL Server.)

Hm, + as string concatenation operator is proprietary to SQL Server. I
don't know that MySQL uses, but in ANSI SQL it's ||.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Gene Wirchenko on 22 Jul 2010 17:10
On Thu, 22 Jul 2010 13:16:16 -0700 (PDT), vncntj
<vincent.s.jones(a)gmail.com> wrote:

>Is a query like this possible? I'm trying to filter out for words
>that exist in a filter table. I'd like to filter for foul language,
>but for example:
>
>"Cat" is in the filter table.
>It should also filter:
>
>
>Recat
>Cat!
>Catyou

You might have a look at
http://en.wikipedia.org/wiki/Scunthorpe_problem
You could be blindsiding yourself.

[snip]

Sincerely,

Gene Wirchenko
From: Plamen Ratchev on 22 Jul 2010 18:05
If I understand the request correctly, here is one method:

SELECT id, name, comment, fk_id
FROM tresponsecomments AS T
WHERE NOT EXISTS (SELECT *
FROM tfilteredWords AS F
WHERE T.comment LIKE '%' + filtered + '%')
ORDER BY id DESC;

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 22 Jul 2010 23:56
SQL is a syntax language; it does not do semantics. Get a text tool
for such problems.

I consulted at a company whose "Network Nanny" censored the word
"cHARDONay" in one of classes.
 | 
Pages: 1
Prev: Case Expression Performance Implication
Next: Send Messages to Broker in Set?