Member Avatar for Frankey

When i do a select on my db. example.

a row with the column 'animal' has the value of 'Horse'


select * from table where animal = 'horSe'

doens't work?!

How to make it work full all variations with capital or non capital?

  • 3 Contributors
  • 2 Replies
  • 114 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by mwasif

Dani AI

Generated

The query failing to match different capitalizations usually means MySQL is doing a case‑sensitive comparison — not that SQL itself is broken. String comparisons in MySQL follow the column (or expression) collation and the column data type. A quick diagnostic is to inspect the column definition and collation. For example:

SHOW CREATE TABLE your_table\G

or

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
  AND TABLE_NAME = 'your_table'
  AND COLUMN_NAME = 'animal';

As hinted, if the column uses a binary or case‑sensitive collation (names ending with _bin or _cs), Horse will not equal horSe.

Options to fix this:

  • Per query: force a case‑insensitive comparison with a COLLATE clause so you do not change schema.
SELECT *
FROM your_table
WHERE animal COLLATE utf8mb4_general_ci = 'horSe';
  • Permanently: change the column to a case‑insensitive collation (preferred if you always want case‑insensitive behavior):
ALTER TABLE your_table
  MODIFY animal VARCHAR(100)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

As suggested, normalizing case in the query (upper/lower) works, but wrapping the column in functions prevents use of a normal index and hurts performance on large tables. A scalable pattern is a stored generated column (indexed) that holds a normalized form:

ALTER TABLE your_table
  ADD COLUMN animal_ci VARCHAR(100) GENERATED ALWAYS AS (LOWER(animal)) STORED,
  ADD INDEX idx_animal_ci (animal_ci);

Then query against animal_ci for fast, case‑insensitive lookups.

References: Character sets and collations, , Generated columns.

Recommended Answers

All 2 Replies

Member Avatar for debasisdas

try this

select * from table where upper(animal) = upper('horSe')

even better to use LIKE for pattern matching.

Edited by debasisdas because: n/a
Member Avatar for mwasif

What is the collation of this column in table definition?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.