David,
I have to admit that I am not sure how ANSI my SQL was, but it works on
MS SQLServer (which is all I had to test it on when I made my
suggestion) using an analogous query on one of my own databases.
MS SQL 2000 does not seem to contain the concat function/operator and
Microsoft claims SQL 2000 is ANSI 92 compliant, so is concat ANSI? I am
interested because I try to write ANSI SQL these days for portability.
As for the order of precedence, I am afraid I am guessing here, but I
would have thought that keywords (such as LIKE) would be evaluated
before their arguments. The issue would then be the order within the
argument and I can't see a problem there.
I am in the process of building a MYSQL and POSTGRES linux server so
that I can get some direct experience of these databases.
Brendan
Zverina, David wrote:
> 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
>
>
--
Brendan
Brendan Dacre
Ph: 9690 1949
Fax: 9690 1949
Mob: 0402 149 422
E-mail: [EMAIL PROTECTED]
E-mail: [EMAIL PROTECTED]
--
SLUG - Sydney Linux User's Group - http://slug.org.au/
More Info: http://lists.slug.org.au/listinfo/slug
