Phoenix: how to make regex on field?

[#1]

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

[#2 · Accepted solution]

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   |
+-----------+-----+

[#3]

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

[#4]

@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%'

[#5]

Now if table has records like

1. +-----------+-----+
2. | S | PK |
3. +-----------+-----+
4. | abcd_bar |1|
5. +-----------+-----+
6. | 1234_bar |1|
7. +-----------+-----+

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

1. +-----------+-----+
2. | S | PK |
3. +-----------+-----+
4. | 1234_bar |1|
5. +-----------+-----+
6. | test_bar |2|
7. +-----------+-----+

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

[#6]

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