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 On Wed, Feb 24, 2021 at 3:02 AM Tuan Quoc <[email protected]> wrote: > 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? > On Wednesday, February 24, 2021 at 2:55:40 AM UTC+7 [email protected] > wrote: > >> 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? >> >> >> On Tue, Feb 23, 2021 at 4:47 PM Tuan Quoc <[email protected]> wrote: >> >>> 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 >>> >>> -- >>> 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 [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/jooq-user/839db656-4869-4df0-aa92-5e39a6d7cc1fn%40googlegroups.com >>> <https://groups.google.com/d/msgid/jooq-user/839db656-4869-4df0-aa92-5e39a6d7cc1fn%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/923bd718-fcd1-444a-ab7b-92ac5b844e16n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/923bd718-fcd1-444a-ab7b-92ac5b844e16n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO50Q7d_KNTQwiyipn-p2SLcEDfe6DMKLBBMZ1fR4Z2uEw%40mail.gmail.com.
