Re: Using LIKE to search for occurence of a column value in a string

[Jeff Moore]

I am having the same problem.

This Query:

SELECT 'haystack needle haystack' LIKE concat('%', 'needle', '%')

returns 1

However, this query does not work correctly If you generalize is to 
include a database column:

SELECT * FROM MyTable WHERE 'haystack needle haystack' LIKE CONCAT('%', 
NeedleColumn, '%')

This query does not work either:

SELECT * FROM MyTable WHERE LOCATE(NeedleColumn, 'haystack needle 
haystack')  0

The two previous queries fail even when an exact match exists:

SELECT LOCATE('haystack needle haystack', 'haystack needle haystack')

returns 1

SELECT * FROM MyTable WHERE NeedleColumn = 'haystack needle haystack'

Returns

NeedleColumn
-
haystack needle haystack
The following query (incorrectly) returns no records:

SELECT * FROM MyTable WHERE LOCATE(NeedleColumn, 'haystack needle 
haystack')  0

As far as I can tell, this is a bug.  can someone suggest a workaround?



On Sunday, March 23, 2003, at 07:23  AM, Jakob Vedel Adeltoft wrote:

I wan't to search for all rows that match a value.

I have these rows:
URL Name
'http://www.microsoft.com/kb/'  Microsoft Knowledgebase
'http://www.microsoft.com/search/'  Microsoft Search
Now I wan't to find all occurences where any of above URL columns 
exist in the string 
'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12'.

I tried to use LIKE:
SELECT URL, Name
FROM websites
WHERE 'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12' LIKE 
(URL + '%');

But this doesn't return any results. I would like the following as 
output:
'http://www.microsoft.com/kb/'	Microsoft Knowledgebase

Plz help me:)

/Jakob

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Using LIKE to search for occurence of a column value in a string

[Jeff Moore]

On Saturday, March 29, 2003, at 07:41  AM, Trevor Smith wrote:

2. your syntax just seems wrong. This should be:

SELECT * FROM MyTable WHERE NeedleColumn LIKE '%needle%';

to search for the string 'needle' anywhere in NeedleColumn, if that's
what you were looking for.
I'm jumping in mid-stream so excuse me if I'm confused.
The syntax was correct for the task.

Lets say for example, that you have a database of bad words:

CREATE TABLE BadWords (Word CHAR( 10 ) NOT NULL);
INSERT INTO BadWords (Word) VALUES ('Fudge');
INSERT INTO BadWords (Word) VALUES ('Dang');
INSERT INTO BadWords (Word) VALUES ('Darn');
You want to validate an arbitrary string against the database to find 
out if it contains any bad words.

example string that contains a bad word: 'Darn Database

To find which bad words were used in the phrase you are checking, You 
would issue a query like this:

SELECT * FROM BadWords WHERE 'Darn Database' LIKE CONCAT('%', Word, '%')

which returns

Word

Darn
When I just tried this small example, it worked perfectly which really 
surprised me. I spent about 2 hours testing this every which way before 
my previous post, too.

Looking into it more, I found that the reason that it didn't work 
before was that I was using PHPMyAdmin under IE for the mac. I browsed 
a table to get a key value to test with and copied the value I wanted 
to search for from the browser and pasted it into my test query.

The string in my test that I thought I was using was:

Mozilla/4.0 (compatible; MSIE 5.02; Windows 98)

When I pasted this string into my text editor and told it to add escape 
codes for hidden characters, I got this:

Mozilla/4.0\0xCA(compatible;\0xCAMSIE\0xCA5.02;\0xCAWindows\0xCA98)

It seems that PHPMyAdmin converts spaces in the query results into 
nbsp; entities for html display.  On the Mac with IE, when you copy a 
string that contains nbsp; from the browser, it seems to get converted 
into the character \0xCA instead of the normal space character.

Everything works fine if I search for the correct string containing 
normal spaces.

I feel stupid for not double checking my queries outside of PHPMyAdmin 
before posting my last message.

Does this help you, Jakob?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]