1

I have the following jpql with a LIKE

SELECT someField FROM someEntity WHERE otherField LIKE '%foo[bar]'

I want to retrieve all rows ending with 'foo[bar]'. For maxDb this is OK. But for sql-server the square brackets are interpreted as wildcards (usually in sql server [a-d] would match all rows containg a,b,c or d inside the brackets). I wonder why hibernate is not interpreting the String 'foo[bar]' as String and excaping the square brackets by itself. The following hibernate dialects are used: for maxDB hibernate.ExtSAPDBDialect and for sql-server org.hibernate.dialect.SQLServer2012Dialect. Right now I have to differentiate in Java between my destination systems, so I can decide if to escape the brackets or not. I wonder why I need to do this in code. Is there a possibility to teach hibernate to consider this, so I can use only the select.

Benjamin Schmidt
  • 11
  • 2

1 Answers1

0

It will add additional overhead as it's one more operation and index seek can't be used, but you can replace these the ] and [ with something else. For example:

REPLACE(someColumn, '[', CHAR(31)) LIKE '%foo' + CHAR(30) + 'bar]'

or you can try using ESCAPE option as it's supported in maxdb, too. In T-SQL, it will be:

DECLARE @someTable TABLE
(
    [someColumn] NVARCHAR(128)
);

INSERT INTO @someTable ([someColumn])
VALUES ('test foo[bar]')
      ,('test foo[bar');

SELECT * 
FROM @someTable 
WHERE someColumn LIKE '%foo|[bar]' ESCAPE '|';
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • Hello gotqn and thank you for your reply, I changed my question, because I think it wasn´t clear, that I´m using jpql. I want hibernate to escape the '%foo[bar]' for me, so I don´t have to do it. For maxDB there is no ESCAPE needed, because I wan the String to be interpreted as is in the jpql. Best regards – Benjamin Schmidt Jul 11 '22 at 08:56
  • Hi @BenjaminSchmidt , are you saying the `Answer is useful`, your Question is solved? – Stefan Wuebbe Jul 11 '22 at 09:05
  • 1
    Hello @StefanWuebbe, the question is not solved. I don´t want to Escape the square brackets, I want hibernate to do this for me (if possible), so that I don´t have to write separate code for maxDB and sql server. – Benjamin Schmidt Jul 11 '22 at 12:01