incubator-couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
Subject Re: Simulating SQL 'LIKE %' using Regular Expressions
Date Wed, 02 Jun 2010 01:56:24 GMT
Understood, BUT what if I had an extra product, your thinking "obviously 
just set the limit to 11" or something like that.
I am wanting it to be a bit more dynamic so that I do not have to know how 
many to limit the results to until it shows something that is not like C%

For example with what you have given me to try, limit=10 works fine, if i 
change it to 11, the 11th value of the key is "Store A"..clearly not LIKE 

I tried endkey and abused unicode, but that resulted in a JSON error.

What I am trying to say (I think) is when I run the view, I want the rows 
returned to ONLY be those LIKE C% so that I do not require a limit at all, 
it just shows all of them based on the query (i.e C, Co, Comp etc etc)

is this just something that is impossible with couchDB? I'm sorry if I 
sound really dumb and noobish, I am very very very new to couchDB and the 
concept of documents as opposed to tables

would something like this ever work? (extract from a tutorial)

The clue lies in extracting the prefix we want to search for from our 
document and put it into our view index. We use a regular expression to 
match our prefix:
function(doc) {
  if(doc["mime-type"]) {
    // from the start (^) match everything that is not a slash ([^\/]+) 
    // we find a slash (\/). Slashes needs to be escaped with a backslash 
    var prefix = doc["mime-type"].match(/^[^\/]+\//);
    if(prefix) {
      emit(prefix, null);
Comment on topic or styleYou can now query this view with your desired 
mime-type prefix and not only find all images, but also text, and video 
and all other formats:

Paul Davis <>
02/06/2010 11:33 AM
Re: Simulating SQL 'LIKE %' using Regular Expressions

The short answer is that you can do 'like "C%"' but not 'like "%C"'.
You don't really appear to care about the latter so this should be ok.

To accomplish:

//map function
function(doc) {
  for(id in doc.Stores) {
    if(doc.Stores[id].name) {
      emit(doc.Stores[id].name, {
        "product": doc.product_name,
        "qty": doc.Stores[id].item_count

And then to query you use something like:


to get the first 10 matching stores.

To get the first N stores matching a prefix of the name for a given
product, do something like:

//in map function
emit([doc.product_name, doc.Stores[id].name], product_info_obj);

and query the view with the query string:

?startkey=[product_name, "C"]&limit=10

Remember that views are all about sorting. Your prefix dealie is just
asking for "the next N things that sort after this prefix" which is
totally doable.

The inevitable follow up will be "but it shows stores that start with
Dude" when I query for "C" if there aren't any stores starting with

In this case you just use an endkey that limits the results to the
range you're interested in. Kinda sorta abusing unicode bits, you can
do something like:

?startkey="C"&endkey="C\uFFFF"&limit=10 which will limit things
reasonably. I seem to recall a different string sentinel but the
alternate version escapes me at the moment.

Also, this should work with things like:


to get the list of stores starting with "Computer"

On Tue, Jun 1, 2010 at 9:01 PM,  <> wrote:
> Hi, so in my couchDB, what I want to happen is when you query a view in
> the design doc and providing ?key="C" at the end, bring up all shop 
> that start with C.
> to do this I have made the following map function:
> function(doc)
> {
>  for (id in doc.Stores)
>  {
>  var strqry = doc.Stores[id]["name"].match(/^.*/);
>    if (strqry)
>    {
>      emit(strqry, doc.product_name + " qty: " +
> doc.Stores[id].item_count);
>    }
>  }
> }
> When I run this from the temp view in Futon, I get a list of all stores
> and products (which made me think 'yay it worked, all it needs is a
> parameter') but when I use:
> http://host:5984/db/design/name/_view/function?key="C" I get back:
> {"total_rows":30,"offset":0,"rows":[]}
> my ultimate aim is to get it working similar to an SQL Like % so if for
> example I say ?key="C" it will return "Computer Store A" and so on..
> I created my function based on this tutorial:

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message