Various ways to query strings?

Question

I'm working on an app that involves a lot of carefully designed strings. I'm in the process of designing the string format and for that I need to know what's possible and what's not when I'm querying the same data.

Which ones of these are possible with MySQL? .. and how do I accomplish them?

1. Results which contain this exact string -- not case sensitive

2. Results which contain this exact string -- case sensitive

3. Results which contain a similar string -- not case sensitive

4. Results which contain a similar string -- but individual characters must be of the same case

Answer 1

1. SELECT this FROM that WHERE LOWER(string) = LOWER("blablabla");
2. SELECT this FROM that WHERE string = "blablabla";
3. SELECT this FROM that WHERE LOWER(string) LIKE LOWER("blablabla");
4. SELECT this FROM that WHERE string LIKE "blablabla";

Hope that's right.

Answer 2

1. Results which contain this exact string -- not case sensitive

2. Results which contain this exact string -- case sensitive

These can both be accomplished. See the page documenting string functions in MySQL, in particular INSTR.

Case sensitivity is determined by the collation of a column. If you want values in a column to be compared in a case-sensitive fashion, then you give it a case-sensitive collation, as in the following example:

ALTER TABLE MyTable ADD MyColumn VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_cs NOT NULL

Conversely, if you want values in a column to be compared in a case-insensitive fashion, then give it a case-insensitive collation.

If you might want values in a column to be compared either way, then there are ways to do that too, though it's slightly more complicated.

3. Results which contain a similar string -- not case sensitive

4. Results which contain a similar string -- but individual characters must be of the same case

Depends what exactly you mean by "similar", but for some values of "similar" yes this is available. You will probably find it useful to consult the page I linked above.