Bug in multi column filter in connection with underscore

[expired user #8841]'s profile image [expired user #8841] posted 8 years ago in General Permalink

Hi,

the multi column filter generates the wrong SQL query when using a filter value that contains reserved characters. For example, type "A_B" and hit "Apply filter". The SQL query will look like

... OR "Name" LIKE '%A_B%'

whereas the escape sequence of _ is not understood by SQL server hence no results.

I'm using the current revision 9.3.0.5026.

So lonG Daniel

ansgar's profile image ansgar posted 8 years ago Permalink

First, you are on SQL server? Which version is it?

Then, how does MSSQL expect the underscore to be escaped, if at all?

[expired user #8841]'s profile image [expired user #8841] posted 8 years ago Permalink

Jeez, i always forget to mention the SQL language. It's T-SQL and the server is running Microsoft SQL Server 2008. The escaping is not needed here. Btw: i did a test with ' and it is escaped correctly as ''.

ansgar's profile image ansgar posted 8 years ago Permalink

I just gave it a test and the filter HeidiSQL generated was:

"autoadmin_id" LIKE '%a\_b%' OR "db_name" LIKE '%a\_b%' ESCAPE '\'

That works here. Doesn't that work on your server?

[expired user #8841]'s profile image [expired user #8841] posted 8 years ago Permalink

Hmm, i have learned something new today:

https://msdn.microsoft.com/en-us/library/ms187489(v=sql.105).aspx

This means you have to escape the value as:

'%A[_]B%'

Btw, the host is running the following SQL Server:

Microsoft SQL Server Enterprise Edition (64-bit)
10.50.6220.0
ansgar's profile image ansgar posted 8 years ago Permalink

Does that work in all other SQL server editions too?

[expired user #8841]'s profile image [expired user #8841] posted 8 years ago Permalink

...since SQL Server 2005 according to the MSDN.

Please login to leave a reply, or register at first.