0

I have a longblob column, which stores text data for some records and binary data for other ones. My database is set as case insensitive. Is there a way to write such a select

select * from myTable where blobCol like '%example%'

in way, where the result contains "EXAMPLE" as well as "example" text?

user3523426
  • 836
  • 3
  • 11
  • 26
  • So you can try like `select * from myTable where blobCol like '%example%' or blobCol='example'` – KMS Dec 12 '17 at 12:28

1 Answers1

0

I don't really know what "My database is set as case insensitive" means in your case, but if you want to use case insensitive search, then I think the best practice is convert the strings to lower case and then compare it:

select * from myTable where LOWER(blobCol) like '%example%'

This code will find all of the following strings: example,Example,EXAMPle,... EXAMPLE

UPDATE: I added also conversion from blob to text as requested:

select * from myTable where LOWER( CONVERT(blobCol USING utf8) ) like '%example%'
Tade
  • 41
  • 4
  • 1
    I don't know what is your actual problem, but maybe you need to convert the blob to text first: https://stackoverflow.com/questions/948174/how-do-i-convert-from-blob-to-text-in-mysql – Tade Dec 12 '17 at 17:41
  • Thanks Tade, that's the magic I was looking for. In case you enter it as an answer, I'll mark it. – user3523426 Dec 12 '17 at 18:23