This will not work because 'like' and '+' have the same precedence and are
evaluated left to right. Hence your expression will evaluate to 1 +
integer_value(BadWords) + 0. Hence your query will return all rows in
Badwords with the exceptions of any words that have integer value of -1. Eg:
'-1', '-1.0', etc. This highlights why you should use concat() to
concatenate strings and not '+'.
To make your query work you could use:
select BadWords FROM WordsTable WHERE 'this is a test' LIKE ('%' + BadWords
+ '%')
Or even more dodgy:
select BadWords FROM WordsTable WHERE '%' + BadWords + '%' LIKE 'this is a
test'.
But it is probably best to stick to the locate() or concat() examples
mention in earliers posts. :)
Following sample queries illustrate some interesting 'features' of mysql.
mysql> select '1' like '1' + '1';
+--------------------+
| '1' like '1' + '1' |
+--------------------+
| 2 |
+--------------------+
1 row in set (0.00 sec)
mysql> select '1' + '1' like '1';
+--------------------+
| '1' + '1' like '1' |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.02 sec)
:)
Cheers,
Dave.
> -----Original Message-----
> From: Brendan Dacre [mailto:[EMAIL PROTECTED]]
> Sent: 13 August 2002 13:37
> To: [EMAIL PROTECTED]
> Cc: Louis Selvon
> Subject: RE: [SLUG] MYSql and Pattern matching Simple Question
>
>
> Louis,
>
> Try this:
>
> select BadWords FROM WordsTable WHERE 'this is a test' LIKE '%' +
> BadWords + '%'
>
> (assuming you do not have a case sensitive database)
>
> Brendan
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of > Louis Selvon
>
> Sent: Tuesday, 13 August 2002 11:47 AM
> To: [EMAIL PROTECTED]
> Subject: [SLUG] MYSql and Pattern matching Simple Question
>
>
> Hi:
>
> I have read about MYSQL "LIKE", and "REGEXP", but am having
> problem doing a simple pattern matching.
>
> Here is what I want to achieve.
>
> I have a string for e.g. called "this is a test" . I would
> like to search the SQL databases to see if any of the words
> in that string can be found in the forbidden words sql
> database. The databases itself just have one column with rows
> of restricted words. A sample of this DB is shown below
>
> -----------
> BadWords (Column name)
> test (row 1)
> aaa (row 2)
> bbb (row 3)
> -----------
>
> I tried the following commands with no success:
>
> 1. select BadWords FROM WordsTable WHERE "this is a test"
> LIKE BadWords; 2. select BadWords FROM WordsTable WHERE "this
> is a test" LIKE "%BadWords%"; 3. select BadWords FROM
> WordsTable WHERE BadWords LIKE "this is a test";
>
> But if I change the string to "test" only with the above
> commands, I get something back (row 1 of table) for commands
> 1. and 3. above.
>
> Any suggestion on how I can get this to work with strings,
> please let me know.
>
> Louis.
>
>
> --
>
>
> --
> SLUG - Sydney Linux User's Group - http://slug.org.au/
> More Info: http://lists.slug.org.au/listinfo/slug
>
This e-mail and any attachments may be confidential or legally privileged.
If you received this message in error or are not the intended recipient, you
should destroy the e-mail message and any attachments or copies, and you are
prohibited from retaining, distributing, disclosing or using any information
contained herein. Please inform us of the erroneous delivery by return
e-mail. Thank you for your co-operation.
Mercer Human Resource Consulting Limited is regulated by the Financial
Services Authority and is a member of the General Insurance Standards
Council. Registered in England No. 984275. Registered Office: Telford House,
14 Tothill Street, London SW1H 9NB
--
SLUG - Sydney Linux User's Group - http://slug.org.au/
More Info: http://lists.slug.org.au/listinfo/slug
