Re: Using LIKE to search for occurence of a column value in a string
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
On Sat, 29 Mar 2003 05:43:53 -0500, Jeff Moore wrote:
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, '%')
Two things:
1. you don't seem to need the CONCAT() function. Just stick the % signs
around the string you're looking for.
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.
--
Trevor Smith|[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
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]
Using LIKE to search for occurence of a column value in a string
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]
RE: Using LIKE to search for occurence of a column value in a string
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 Hi! How about the following? SELECT URL, Name FROM websites WHERE 'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12' LIKE CONCAT(URL, '%'); - Ville -- 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
I thought you only could use the likesyntax like this; SELECT column from table where column like '%data%'; i have a hard time seeing what goodit would be calling a column http:// =) check; http://www.mysql.com/doc/en/Pattern_matching.html regards lasse On Sun, 23 Mar 2003, Ville Mattila wrote: 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 Hi! How about the following? SELECT URL, Name FROM websites WHERE 'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12' LIKE CONCAT(URL, '%'); - Ville -- -BEGIN GEEK CODE BLOCK- Version: 3.1 (2002 update) GCM/GCS/GMU/ d+ s++:++ a-- C++() UBLAIS+() P+ L+(-) E--- W+++ N+ o-- K w O- M- V- PS(+++) PE++ Y PGP- t 5+ X++ R- tv+ b+ DI+++ D- G e h++ r% y++ --END GEEK CODE BLOCK-- -- 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
The reason that what you are doing isn't working is because you are
trying to find a really long string in a short string. You need to
reverse your string searching.
Try:
SELECT URL, Name
FROM websites
WHERE LOCATE(URL,
'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12') 0;
Here's some other functions that might be of interest:
LOCATE(substr,str)
POSITION(substr IN str)
Returns the position of the first occurrence of substring substr in
string str . Returns 0 if substr is not in str :
mysql SELECT LOCATE('bar', 'foobarbar');
- 4
mysql SELECT LOCATE('xbar', 'foobar');
- 0
This function is multi-byte safe. In MySQL 3.23 this function is
case sensitive, while in 4.0 it's only case-sensitive if either
argument is a binary string.
LOCATE(substr,str,pos)
Returns the position of the first occurrence of substring substr in
string str , starting at position pos . Returns 0if substr is not in
str :
mysql SELECT LOCATE('bar', 'foobarbar',5);
- 7
This function is multi-byte safe. In MySQL 3.23 this function is
case sensitive, while in 4.0 it's only case-sensitive if either
argument is a binary string.
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in
string str . This is the same as the two-argument form of LOCATE() ,
except that the arguments are swapped:
mysql SELECT INSTR('foobarbar', 'bar');
- 4
mysql SELECT INSTR('xbar', 'foobar');
- 0
This function is multi-byte safe. In MySQL 3.23 this function is
case sensitive, while in 4.0 it's only case-sensitive if either
argument is a binary string.
At 20:07 +0200 3/23/03, Ville Mattila wrote:
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
Hi!
How about the following?
SELECT URL, Name FROM websites WHERE
'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12' LIKE CONCAT(URL,
'%');
- Ville
--
_ ____ +--+
/ | / /__ _/ /_ _|Jeff Shapiro |
/ |/ / _ \/ __ \/ ___/ __ \/ __ `/|Photography and Graphic Design|
/ /| / __/ / / (__ ) / / / /_/ / |Colorado Springs, CO, USA |
/_/ |_/\___/_/ /_//_/ /_/\__,_/ |www.nensha.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
Thanks!
I know I'm searching for for a long string within a short string - but that's why I
reversed the expression from column LIKE(value) to value LIKE(column)
But I think I just might go for your solution using the LOCATE function as it seems to
better fit this specific need.
/Jakob
-Original Message-
From: Jeff Shapiro [mailto:[EMAIL PROTECTED]
Sent: Sunday, March 23, 2003 7:46 PM
To: [EMAIL PROTECTED]
Subject: RE: Using LIKE to search for occurence of a column value in a
string
The reason that what you are doing isn't working is because you are
trying to find a really long string in a short string. You need to
reverse your string searching.
Try:
SELECT URL, Name
FROM websites
WHERE LOCATE(URL,
'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12') 0;
Here's some other functions that might be of interest:
LOCATE(substr,str)
POSITION(substr IN str)
Returns the position of the first occurrence of substring substr in
string str . Returns 0 if substr is not in str :
mysql SELECT LOCATE('bar', 'foobarbar');
- 4
mysql SELECT LOCATE('xbar', 'foobar');
- 0
This function is multi-byte safe. In MySQL 3.23 this function is
case sensitive, while in 4.0 it's only case-sensitive if either
argument is a binary string.
LOCATE(substr,str,pos)
Returns the position of the first occurrence of substring substr in
string str , starting at position pos . Returns 0if substr is not in
str :
mysql SELECT LOCATE('bar', 'foobarbar',5);
- 7
This function is multi-byte safe. In MySQL 3.23 this function is
case sensitive, while in 4.0 it's only case-sensitive if either
argument is a binary string.
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in
string str . This is the same as the two-argument form of LOCATE() ,
except that the arguments are swapped:
mysql SELECT INSTR('foobarbar', 'bar');
- 4
mysql SELECT INSTR('xbar', 'foobar');
- 0
This function is multi-byte safe. In MySQL 3.23 this function is
case sensitive, while in 4.0 it's only case-sensitive if either
argument is a binary string.
At 20:07 +0200 3/23/03, Ville Mattila wrote:
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
Hi!
How about the following?
SELECT URL, Name FROM websites WHERE
'http://www.microsoft.com/kb/knowledgeb.asp?id=3strse=12'
LIKE CONCAT(URL,
'%');
- Ville
--
_ ____
+--+
/ | / /__ _/ /_ _|Jeff Shapiro
|
/ |/ / _ \/ __ \/ ___/ __ \/ __ `/|Photography and
Graphic Design|
/ /| / __/ / / (__ ) / / / /_/ / |Colorado Springs, CO,
USA |
/_/ |_/\___/_/ /_//_/ /_/\__,_/ |www.nensha.com |||
[EMAIL PROTECTED]|
+--+
--
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]
