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.
You don't need to escape " here, so the following should work:
charindex('"status":"solved"', child_events)>0
For example: SqlFiddle