Joker chars

kalvaro posted 4 years ago in General

I've just read this in r4403:

MSSQL wants single quotes escaped with a second single quote, not with a backslash. No clue how to escape joker chars in a WHERE clause.

Not sure if you referred to the ESCAPE clause:

ansgar posted 4 years ago
Ah... thanks. I found details about the ESCAPE clause here, but I supposed this applies to a whole WHERE clause without a LIKE, and got an SQL error when testing that. In fact the ESCAPE syntax seems only valid for a LIKE clause. In that case I have an idea how to fix that - appending the ESCAPE clause to the two or three places where HeidiSQL has LIKE clauses.
ansgar posted 4 years ago
Exactly what I think here...
kalvaro posted 4 years ago
This is all about searching for literal _ or % characters with the LIKE operator (versus being wildcards). You can use the DBMS default separator or append a ESCAPE clause you set your own separator.

The default separator varies between different DBMS but the ESCAPE clause works the same way in MySQL and SQL Server (or even in Oracle).


I admit I was sceptical about the whole SQL Server feature but I'm actually using HeidiSQL with SQL Server 2005 for a project this week so I might report some issues if I find them.
ansgar posted 4 years ago
What I only find really cumbersome in MSSQL is that the ESCAPE clause does not have a default value. So, when you don't apply the ESCAPE clause, the server assumes nothing is escaped. And then of course the duplicated single quote is an exception to that logic. That's indeed something which could be more simple for MSSQL users.

However, nailed in r4404.

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