Wildcards , like

30 views
Skip to first unread message

Luc Evers

unread,
Apr 29, 2016, 8:07:42 AM4/29/16
to CockroachDB user group

     It seems that CockroachDB has no SQL 'like' or 'wildcard' commands (where statement)?

    select * from test;
+----+-------+
| id | info  |
+----+-------+
|  1 | test1 |
|  2 | test2 |
|  3 | test3 |
|  4 | test4 |
|  5 | test5 |
+----+-------+


       select * from test where info like 'test%'
       select * from test where info = 'test%'
       select * from test where info  = 'test*'

  Wildcards in the 'where' statement, will this be possible in the future?


 

Radu Berinde

unread,
Apr 29, 2016, 8:17:31 AM4/29/16
to Luc Evers, CockroachDB user group
Hi Luc,

The first version of the query should work. It does for me:

CREATE TABLE test (id INT PRIMARY KEY, info STRING);
INSERT INTO test VALUES (1, 'test1');
INSERT INTO test VALUES (2, 'test2');
INSERT INTO test VALUES (3, 'test3');
INSERT INTO test VALUES (4, 'test4');
INSERT INTO test VALUES (5, 'test5');

select * from test where info like 'test%';

+----+-------+
| id | info  |
+----+-------+
|  1 | test1 |
|  2 | test2 |
|  3 | test3 |
|  4 | test4 |
|  5 | test5 |
+----+-------+


-Radu

--
You received this message because you are subscribed to the Google Groups "CockroachDB user group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cockroachdb-us...@googlegroups.com.
To post to this group, send email to cockroac...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cockroachdb-users/c22e4d50-3983-489d-b678-b8a14bbb56e2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Peter Mattis

unread,
Apr 29, 2016, 9:07:49 AM4/29/16
to Radu Berinde, Luc Evers, CockroachDB user group
We also support `SIMILAR TO` for regex matching:

root@:26257> select * from test where info similar to 'test[1-4]';
+----+-------+
| id | info  |
+----+-------+
|  1 | test1 |
|  2 | test2 |
|  3 | test3 |
|  4 | test4 |
+----+-------+

Luc Evers

unread,
Apr 29, 2016, 10:17:26 AM4/29/16
to CockroachDB user group


Op vrijdag 29 april 2016 15:07:49 UTC+2 schreef peter:

-Radu




  Not really

                 There are more possibilities with wildcards!
  

Luc Evers

unread,
May 1, 2016, 4:00:53 AM5/1/16
to CockroachDB user group
 I found a CockroachDB function:

  regexp_extract(stringstring)

  Do you have an example how to use this function?
  Can I put here regular expressions?

  




Op vrijdag 29 april 2016 16:17:26 UTC+2 schreef Luc Evers:

Nathan VanBenschoten

unread,
May 1, 2016, 4:48:57 AM5/1/16
to Luc Evers, CockroachDB user group
Hi Luc,

REGEXP_EXTRACT works in identical manner to the SUBSTRING function with the same pair of string parameters. The first argument is the string to be searched, and the second argument is a regular expression pattern to match within the first string. The function will then return the matching portion of the first string. An example of its use is: 

SELECT SUBSTRING('cockroaches' from '^\w{9}') || REGEXP_EXTRACT('scalability', 'l.{2}') || SUBSTRING('consistency', 4, 1);
----
cockroachlabs

We're actively improving our SQL documentation, and better function definitions with examples are close on our radar. Until then, feel free to ask if you have any other questions.

--
Nathan

Luc Evers

unread,
May 1, 2016, 5:12:57 AM5/1/16
to Nathan VanBenschoten, CockroachDB user group
  Nathan Thanks!

   

Raphael 'kena' Poss

unread,
May 1, 2016, 5:13:26 AM5/1/16
to Luc Evers, CockroachDB user group
Hi Luc,

You can find examples of how to use the various functions in sql/testdata.

That said, you probably don't need to use regexp_extract() if you only need to search for likeness. Like Peter explained the SIMILAR TO operator does this, you can use it instead of LIKE.

regexp_extract() does something different: it extracts the sub-string that matches a regex pattern between parentheses.

Regards,


Luc Evers <luce...@gmail.com> schreef op 1 mei 2016 10:00:52 CEST:

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Luc Evers

unread,
May 1, 2016, 5:15:55 AM5/1/16
to Raphael 'kena' Poss, CockroachDB user group
 Oke, I'll check. Thanks Raphael!
Reply all
Reply to author
Forward
0 new messages