Find string occurrences

trogster · #1 (**permalink**) 11-18-09, 18:19

Hi, I need to do something with sql and I am not quite sure how to do it.

I have a variable string (urls), and I need to check if that string contains any of the keywords that are stored in a table.

For example, I have a table with the entries "keywordX1", "keywordX2", "keywordX3", etc.
Then I have a string such as "http://www.domainwithkeywordX2.com/path/?param=value

How can I quickly find if the url string contains (and which ones) keywords from the table using a sql query? (or any other fast method)

I can't select all the keywords and loop that because in the future the keywords list will be huge, and I need speed.

Thanks.

r937 · #2 (**permalink**) 11-18-09, 22:24

is there a table involved here?

usually SQL is written against tables, but the only table you mentioned is an unnamed table with a column called "entries" which has keywords in it

please do a SHOW CREATE TABLE for each table involved here

trogster · #3 (**permalink**) 11-18-09, 23:09

I currently have a table named targets with the columns ID int(11) (primary auto_increment), and keyword varchar(255).

I want to do a sql query and check if my string var (ex. google.com/?q=qwerty) contains any of the keywords in the targets.keyword table.

r937 · #4 (**permalink**) 11-18-09, 23:20

Code:

SELECT DISTINCT 'yes'
  FROM targets
 WHERE 'google.com/?q=qwerty'
       LIKE CONCAT('%',keyword,'%')

trogster · #5 (**permalink**) 11-19-09, 01:39

Quote:

Originally Posted by r937

Code:

SELECT DISTINCT 'yes'
  FROM targets
 WHERE 'google.com/?q=qwerty'
       LIKE CONCAT('%',keyword,'%')

Dude!
Thank you very much, it's 3.38am and I couldn't sleep thinking about how to do this, you saved me.

Internet Services

Web Development

Digital Marketing

Consumer Tech