question Re: Phoenix: how to make regex on field? in Archives of Support Questions (Read Only)

Phoenix: how to make regex on field?

michelsumbul — Wed, 10 Aug 2016 02:14:29 GMT

Hello,

I have a phoenix view on an existing hbase table. I don't find in the documentation how to select every lines of a table where the column "name" match with a specific regex or function on string like contain, start with, etc. For example in "traditional sql": select name,phone from table1 where like "%ab%; or every ligne with a rowkey that start with "blabla".

Thanks in advance to point me how to do it easily (I hope that's not needed to use UDF...)

Michel

Re: Phoenix: how to make regex on field?

elserj — Wed, 10 Aug 2016 02:16:55 GMT

See http://phoenix.apache.org/language/index.html#condition. Your "traditional sql" approach works with Apache Phoenix.

> create table foo(s varchar, pk integer not null primary key);
> upsert into foo values('abcd_bar', 1);
> upsert into foo values('dcba_bar', 2);
> select * from foo where s like 'abcd%';

This will return

+-----------+-----+
|     S     | PK  |
+-----------+-----+
| abcd_bar  | 1   |
+-----------+-----+

Re: Phoenix: how to make regex on field?

ssoldatov — Wed, 10 Aug 2016 02:21:46 GMT

@Michel Sumbul you may find all functions here https://phoenix.apache.org/language/functions.html and of course you may use 'like' keyword, so it will be as a regular sql query:

select name, phone from table1 where name like '%Smith%'

Re: Phoenix: how to make regex on field?

michelsumbul — Wed, 10 Aug 2016 02:31:53 GMT

Ok super thanks, I didn't try because it was not refer in the documentation that the function like work. 🙂

Re: Phoenix: how to make regex on field?

tej_kitkat — Wed, 22 Feb 2017 14:15:27 GMT

Now if table has records like

+-----------+-----+
| S | PK |
+-----------+-----+
| abcd_bar |1|
+-----------+-----+
| 1234_bar |1|
+-----------+-----+

And i want to search only 1234_bar(starting with digits), then how should i write the queries in Phoenix.

If Table records are like

+-----------+-----+
| S | PK |
+-----------+-----+
| 1234_bar |1|
+-----------+-----+
| test_bar |2|
+-----------+-----+

And now if i want to search only 1234_bar then how should i write Select query ?

Re: Phoenix: how to make regex on field?

elserj — Wed, 22 Feb 2017 23:36:06 GMT

Do not piggy-back on others' questions which have already been answered, please. Ask your own question.