Query from ejabberd module with LIKE %%

Question

I have this lines of code in ejabberd module, it works fine:

case catch ejabberd_odbc:sql_query(Server,["select COUNT(*) as total from spool where username='",IdUsername,"' AND xml LIKE '%message from%' AND xml LIKE '%chat%';"]) of
            {selected, [<<"total">>], [[Totale]]} ->
                Count = binary_to_list(Totale);
            _ -> Count = "0"
    end,

If I convert this:

LIKE '%chat%';

with this:

LIKE '%type=\'chat\'%';

I obtain an error, any ideas? or there's another way to get only the chat message?

Answer 1

Since you're typing this in an Erlang string, the Erlang escape sequences apply. In particular, \' is an escape sequence for just a single quote, '. (That's more useful inside atoms, which are delimited by single quotes.)

You can try it in an Erlang shell, and see that "\'" and "'" are equivalent:

1> "\'".
"'"
2> "\'" =:= "'".
true

To include an actual backslash in the string, escape it with another backslash:

"\\'"

In your case, that would be:

LIKE '%type=\\'chat\\'%';