CODEWITHSUNDEEP
mysql
reefine
reefine

Reputation: 853

Compare Two Strings in MySQL

My current query (doesn't work)

SELECT url
FROM table
WHERE
    'http://www.longurl.com/some/string' like '%' . url . '%'

table

url
longurl.com

I am trying to match the record in "table" for longurl.com to any compared URL that contains longurl.com.

Should match compared URLs such as:

http://www.longurl.com/some/string
http://longurl.com
http://longurl.com/some/string

In this PHP example, it is very easy to compare:

$url = "http://www.longurl.com/some/string";    
if(strstr($url, 'longurl.com')) {
    echo "success";
} else {
    echo "failure";
}

Upvotes: 2

Views: 9725

Answers (7)

domager
domager

Reputation: 769

Are you looking for:

LIKE '%longurl.com%'

Upvotes: 0

MikeTheReader
MikeTheReader

Reputation: 4190

Okay, what about: 

SELECT url 
  FROM table 
 WHERE INSTR('http://www.longurl.com/some/string', url) > 0

(Formatting didn't work so well in the comments, so I added it again as an answer.)

Upvotes: 6

MikeTheReader
MikeTheReader

Reputation: 4190

What about this:

SELECT url 
FROM table
WHERE url LIKE '%longurl.com%string%'

or, to be just like your PHP:

SELECT url 
FROM table
WHERE url LIKE '%longurl.com%'

Upvotes: 0

BenMorel
BenMorel

Reputation: 36514

The concatenation operator . does not exist in MySQL, you have to use the CONCAT() function instead:

SELECT url
FROM table
WHERE 'http://www.longurl.com/some/string' LIKE CONCAT('%', url, '%');

Upvotes: 0

braindamage
braindamage

Reputation: 2256

this?

SELECT url
FROM table
WHERE
    url like '%http://%longurl.com%'

or you can also use %longurl.com%

Upvotes: 0

Brian Patterson
Brian Patterson

Reputation: 1625

Try removing or escaping some of the characters in your URL string. OR omitting the http: part .. I believe the : could be confusing the parser ... 

so try   %//longurl.com/%

or even 

%longurl.com%  if possible

or even 

http://longurl.com/%

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

If what you need is getting all URLs containing longurl.com then try using:

SELECT url
FROM table
WHERE
url like '%longurl.com%'

It should work.

Upvotes: 0

Related Questions