Search code examples
mysqlcharsql-likeconcatenationvarchar

Using Like and Concat with Char Type

I have a MySQL statement that is trying to find if the ID of a row (CHAR(36)) is inside of a string.

My statement is :

select * from `options` 
 where concat('%',`OptionID`,'%') 
 like '{\"4d519a3d-c99b-11e4-8eda-b8ca3a83b4c8\":\"62.50\"}';

But this fails and doesn't return anything, while this 

select * from `options` where
 `OptionID`='4d519a3d-c99b-11e4-8eda-b8ca3a83b4c8';

works and returns my row. I thought this was because the ID field was a CHAR field instead of a VARCHAR field so I tried this instead 

select * from `options` where 
 concat('%',cast(`OptionID` as char(38)) ,'%') 
 like '{\"4d519a3d-c99b-11e4-8eda-b8ca3a83b4c8\":\"62.50\"}';

But that still didn't work, and now I'm lost. I would like to avoid iterating through the json string and creating a set of conditions like

(`OptionID`={$JsonKey[0]} OR `OptionID`={$JsonKey[1]} OR ... )

Since these json strings can get very large, but if that it is the best option because of performance or what I'm trying to do can't be done any other way, then I'll just do it that way instead.

Solution

  • The concept will work, but you're doing it backwards. You need to reverse the fields.

    Something like this:

    select * 
from `options` 
where '{\"4d519a3d-c99b-11e4-8eda-b8ca3a83b4c8\":\"62.50\"}'   
      like concat('%',`OptionID`,'%')