Jump to content

Search numbers


Recommended Posts

my problem is ive got a list of rows that associate a video id


select * from video where word_id IN(1,3)


Which returns


word_id      vid_id


1 864144

3 864144

3 864145

1 864146


Basically from this set i only want to return rows that have both numbers in this case vid_id 864144, im just not sure how to go about it

Link to comment
Share on other sites

modify your REGEX statement to look like this:

"WHERE $column REGEXP '[[:<:]]" . $number . "[[:>:]]' = 1"


Using "[[:<:]]" makes the engine look for non alphanumeric characters as word boundaries. It's pretty darn fast, and it should tear into those comma delimited columns without a problem... especially if they are indexed.

Link to comment
Share on other sites

One more quick question: Is there an alternative for IN() using REGEX


Currently i have:

select * from video WHERE id IN(2,7,18,19)


Not to the best of my knowledge, but I do have a php query builder class that parses search strings like:

this_is_treated_as_an_OR_string "this is a quote" +this_is_something_that_has_to_be_in_the_results -i_dont_want_this wildca%rd -"I don't want this quote in my results"


and automatically converts them into a regex string. It's a module in what will be an open source searching class I'm building that I can send you. That would obviously have way more functionality than you require, but it's a nice little bit of code.


I just whipped up a really basic interface to it here:


I can't guarantee that I'll have that up permanently.


I'd be happy to send you the code if you like. Otherwise, the style query that you are looking for is like this:

SELECT * FROM `table` WHERE `column` REGEXP '[[:<:]]or1[[:>:]]' = 1 OR `column` REGEXP '[[:<:]]or2[[:>:]]' = 1

Link to comment
Share on other sites

OK, because REGEX is doing the actual search i dont need to replace IN() because i can limit the amount to 30 items at a time. Its pretty fast on 50 Million rows it returning a set in 0.0006 secs


This is equivilant to "some words" in boolean

select id from video WHERE word_id REGEXP '[[:<:]]567,765[[:>:]]' = 1 limit 0, 30


This is equivilant to find rows that contain at least one of the two words in boolean

select id from video WHERE word_id REGEXP '[[:<:]](567|765)[[:>:]]' = 1 limit 0, 30


The final one i need is '+some +words' boolean equivilant aka match both numbers within the string, any suggestions?


Link to comment
Share on other sites

  • 2 weeks later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.