Loading...
X

Case sensitive search in phpMyAdmin

Table of contents

1. Case sensitive search in phpMyAdmin

2. How to create a request with “BINARY” in phpMyAdmin

3. How can I make SQL case sensitive string search in phpMyAdmin

4. Video on how to find a case-sensitive string in phpMyAdmin


Case sensitive search in phpMyAdmin

Let’s try to find rows in the table that contain the word “test”.

As an example we will use the following test table:

We go to the “Search” tab. We will conduct the search in the test_column column, selecting “LIKE %…%” as the operator (means searching for all lines that contain the searched string). Enter “test” as the Value (in this case, the search string).

Click the “Go” button. As you can see, records with the search word were found, but the results are not what one would expect. Instead of matching the search string exactly, it also shows results that match strings in a different case: “TEST”, “Test” and “tEST”.

To go deeper into the details of why this happens and what to do, we recommend checking out the article “Case sensitive search in MySQL/MariaDB”.

In short, searches for data types CHAR, VARCHAR, TEXT are case insensitive.

To make the search case sensitive, you need to:

1. The data in the column was of type BINARY, VARBINARY or BLOB

2. Either add the word “BINARY” before the search string (the article above explains in detail why this works)

I don't think you would want to change the data type of the columns for table lookups. Therefore, to make the search case sensitive, you need to add “BINARY”.

How to create a request with “BINARY” in phpMyAdmin

Despite the abundance of explicit and hidden functions in the depths of the menu, in phpMyAdmin there is no such button or checkbox when clicked on which the search would become case sensitive. We need to go a little roundabout.

How can I make SQL case sensitive string search in phpMyAdmin

1. To begin, search for the string you need and use the “LIKE %…%” operator

2. A page with case-insensitive search results will be shown. An “Edit” button will appear above the results – click it.

3. The SQL query that was used to search for the data will be shown.

4. After “LIKE”, add the word “BINARY” separated by a space.

5. Click the “Go” button.

Now only case-sensitive search results will be shown.

Video on how to find a case-sensitive string in phpMyAdmin


Leave Your Observation

Your email address will not be published. Required fields are marked *