How to implement Contains(field, value) in JOOQ

[Tuan Quoc]

Hi Lukas,

I am trying to implement Contains(field, value) by using DSL.field("fieldName").contains("value") and it will be generated like this: 

fieldName like ('%' + replace(replace('"value*"'), '!', '!!'), '%', '!%'), '_', '!_' + '%') escape '!'

When I run the query with Contains(fieldName, value), there are multiple rows affected but when I tried running the generated query above, there is no rows affected.

Do you have any solutions for this? 

Thanks a lot in advance.

Thanks

Tuan

[Lukas Eder]

Hi Tuan,

I'm sorry, I'm not sure I understand what you mean. Can you show a more specific example with some example data?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/839db656-4869-4df0-aa92-5e39a6d7cc1fn%40googlegroups.com.

[Tuan Quoc]

When I run this code: DSLContext.selectFrom(tableName).where(DSL.field("fieldName").contains("value")) -> the query is generated like this:
(1)  Select * from tableName where fieldName like ('%' + replace(replace('"value*"'), '!', '!!'), '%', '!%'), '_', '!_' + '%') escape '!'

But the desired query is like this:
(2) Select * from tableName where Contains(fieldName, '"value*"')

The result of runnning query (1) is different from running query (2)

How do I use JOOQ to generate the query that looks like (2). Do you have any solutions for this?

[Rob Sargent]

Which sql dialect has a “contains” function?

On Feb 23, 2021, at 7:02 PM, Tuan Quoc <nlquoct...@gmail.com> wrote:

When I run this code: DSLContext.selectFrom(tableName).where(DSL.field("fieldName").contains("value")) -> the query is generated like this:

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/923bd718-fcd1-444a-ab7b-92ac5b844e16n%40googlegroups.com.

[Tuan Quoc]

Hi robjs,

I am using SLQServer2012 dialect.

[Rob Sargent]

Pretty sure the jooq "contains" is really a substring search (it's java
after all), whereas MSSQL "contains" if a fulltext extention. Quite
likely not implemented in a generic tool such as jooq. Unless it's
hidden in the MSSQL dialect somewhere. If so you would need to cast you
DSL to that dialect explicitly. You may have to revert plain sql to get
that "contains(column, value)" function to work.

[Rob Sargent]

er, jooq "Field.contains()" works on arrays as well

On 2/23/21 7:55 PM, Tuan Quoc wrote:

[Lukas Eder]

I see, so this is some kind of native SQL version of contains(). Ours is a simplified approach to writing "LIKE" predicates, just like Java's String.contains().

As always, when jOOQ does not support a vendor specific feature, you'll resort to rolling your own plain SQL template:

https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating

We could retrofit our Field.contains() method for this purpose, but I think it would be confusing to have it implement several semantics. Besides, even SQL Server users might use *our* contains, rather than SQL Server's as a simple "contains" function.

I hope this helps,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/923bd718-fcd1-444a-ab7b-92ac5b844e16n%40googlegroups.com.