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

[Jeff Shapiro]

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=3&strse=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=3&strse=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=3&strse=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]