Configure hibernate to process like with [] (square brackets) against maxDB and sql-server in the same way

user_19510629 0
Viewed 59

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.

maxdb wildcard sql-like hibernate sql-server
1 Answers

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 '|';
user_10803540
Related