How to support Sql string operations IN and Regex

[mrinalkamboj <mr...@gmail.com>]

Following is my current query for *OrderCache*, using a Sql *like* operator

*var fieldsQuery = orderCache.QueryFields(new SqlFieldsQuery("select Top 10
OrderId, OrderName from OrderEntity Where OrderName like '%' || ? || '%'
Order By OrderId", orderNameValue));*

As per my understanding this will help in creating query for StartsWith,
EndsWith, Contains, as the '%' can be accordingly placed, but how shall we
create a query for IN operator, where we need to supply a collection and
check the existence of a value. Can the current placeholder take a
collection or would it be:

*OrderName IN 'A,B,C,D,E'*

Also how to support the regular expression, I need all the values for a
field which match the following Regular expression:

@"^(?:A|B)-*(?:C|D)-(?:E|F)$"

Following values shall match:

A-K-C-E
B-D-F

Following shall not match:

E-D-F

Any pointer that can help me devise these queries



--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-support-Sql-string-operations-IN-and-Regex-tp10760.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: How to support Sql string operations IN and Regex

[Pavel Tupitsyn <pt...@apache.org>]

IN queries and their alternative with temporary table are described there:
https://apacheignite.readme.io/docs/sql-performance-and-debugging#section-sql-performance-and-usability-considerations

Ignite uses H2 SQL engine, which supports regular expressions:
http://www.h2database.com/html/functions.html#regexp_like

Pavel

On Tue, Feb 21, 2017 at 5:12 PM, mrinalkamboj <mr...@gmail.com>
wrote:

> Following is my current query for *OrderCache*, using a Sql *like* operator
>
> *var fieldsQuery = orderCache.QueryFields(new SqlFieldsQuery("select Top 10
> OrderId, OrderName from OrderEntity Where OrderName like '%' || ? || '%'
> Order By OrderId", orderNameValue));*
>
> As per my understanding this will help in creating query for StartsWith,
> EndsWith, Contains, as the '%' can be accordingly placed, but how shall we
> create a query for IN operator, where we need to supply a collection and
> check the existence of a value. Can the current placeholder take a
> collection or would it be:
>
> *OrderName IN 'A,B,C,D,E'*
>
> Also how to support the regular expression, I need all the values for a
> field which match the following Regular expression:
>
> @"^(?:A|B)-*(?:C|D)-(?:E|F)$"
>
> Following values shall match:
>
> A-K-C-E
> B-D-F
>
> Following shall not match:
>
> E-D-F
>
> Any pointer that can help me devise these queries
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/How-to-support-Sql-string-operations-
> IN-and-Regex-tp10760.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>