cancel
Showing results for 
Search instead for 
Did you mean: 

Escaping special characters in FlexibleSearch queries

former_member1336901
Participant

How can I escape special characters (e.g. "{", "}" or "?") in FlexibleSearch queries?

E.g. I want to run

SELECT * FROM {Order} WHERE {attribute} REGEXP 'some regexp with ?, { or }'

(the REGEXP operator is MySQL syntax).

That doesn't work, because Hybris interprets those characters and complains about that, even inside of string literals. That looks like a bug to me. How can I escape those characters?

I know that I could use a bind variable in this case, but how would it work without a bind variable?

Accepted Solutions (0)

Answers (7)

Answers (7)

Former Member
0 Kudos

Hi Thomas, unfortunately, as said in the answers there's no way to escape a special character on the FlexibleSearch directly, but I found a workaround to the issue using for instance the char() function that several database give you and concatenating the string together. I'll give you an example: I had to extract a value from a field of a table using a regexpr that has the '{' character in it. Actually my first attempt was this one: select SUBSTR_REGEXPR('{(.*)}' in {parameters} GROUP 1)
from {LoggingUserAction}

but as you may guess, the query didn't work because the parser couldn't interpret in the correct way the '{' and '}' of the regular expression. So this is how I changed it:

select SUBSTR_REGEXPR(concat(concat(char(123),'(.*)'),char(125)) in {parameters} GROUP 1)
from {LoggingUserAction}

In this way, the parser didn't complain anymore and the correct result was extracted.

Former Member
0 Kudos

Does anyone know if this has been fixed in the FlexibleSearch API? I'm trying to search for fields that have an underscore in them with LIKE '%_%', but since underscore is also a special character I need to escape it somehow. Again: I want to do this with flexible search in the hac, not with SQL and not with some parameter passing in code.

former_member1336901
Participant
0 Kudos

This is not a FlexibleSearch problem, but how the LIKE operator works, so it's an SQL problem. The LIKE operator treats "%" and "_" specially ("%" means any string, including the empty string, "_" means any character). Depending on the underlying database, you can configure an escape character for those special characters. E.g. for MySQL see http://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#operator_like

For MySQL the escape character is "\" by default, so you should be able to use

LIKE '%\_%'

to match all values which have an underscore in them.

Former Member
0 Kudos

I have a value as '{B57B2719-34F7-14FE-EB3F-76228656CA79}' and it seems the escape \ is not working in this case any idea why? is it so because curly braces are reserve character for flexible search?

former_member1336901
Participant
0 Kudos

Yes, "{" is special, and no escaping is possible. The only way to use those characters ("?", "{" and "}") is to use bind variables.

As written in an earlier comment: I'm surprised that Hybris interprets those special characters within string literals. Clearly it should not do that, only outside of string literals.

Former Member
0 Kudos

Hi,

after i got the same challenge here is a possible solution:

StringUtils.replace(query, "?", "\\?");

Former Member
0 Kudos
former_member1336901
Participant
0 Kudos

In other words: no escaping is possible, you can only use special characters via bind variables (my question was how it can be done without bind variables).

How silly is that? Especially for ad-hoc queries in the HAC (where you cannot use bind variables), there is no workaround (except for using a BeanShell/Groovy script).

It would be very easy for the FlexibleSearch query parser to ignore the special characters in string literals.

Former Member
0 Kudos

Hello, special character can be escaped with backslash. trying the above sql like this SELECT * FROM {Order} WHERE {attribute} REGEXP 'some regexp with \?'

former_member1336901
Participant
0 Kudos

Sorry, I didn't mention that: Escaping with backslashes was the first thing I tried. It doesn't work. I tried it in the HAC.

I tried running this silly query:

SELECT * FROM {Order} WHERE {code}='\?'

This gives me

Exception message: missing values for [], got {11=8796130181202, 10=8796130115666, 12=8796095447122}

Former Member
0 Kudos

Hello,

I seems to be a bug on the flexible search api, try to do it with natif SQL.

BR

former_member1336901
Participant
0 Kudos

Native SQL works, yes. Not a nice workaround, though. :)