Search code examples
sql-servercharindex

SQL Server charindex() appears to fail - does it include punctuation?

I converted from a MySQL database to SQL Server.

In one column, I'm looking for a string:

"status":"solved"

Note that the quotes are literally in the column.

In MySQL, I have the function ---

instr(child_events, '\"status\":\"solved\"')>0

Note that I am escaping the double quotes (all four) with a backslash. This works just as expected.

Now, I have the exact same data in SQL Server (verified).

I am performing the function: 

charindex('\"status\":\"solved\"', child_events)>0

and also

charindex('""status"":""solved""', child_events)>0

This is failing. It's bringing back a handful of rows (maybe 2% of the actual with the first, 0% with the second). But then I say simply find:

solved

Then it finds all the results.

What is going on here? Why isn't charindex working? The column data type is varchar(max) --- I had converted it from text data type initially (I'm used to MySQL) -- so maybe that didn't work. The column length can also be quite long --- possibly up to 300 or 400 characters. Any thoughts much appreciated.

Solution

  • You don't need to escape " here, so the following should work:

    charindex('"status":"solved"', child_events)>0

    For example: SqlFiddle