0 votes
1 view
in SQL by (20.3k points)

I looked around some and didn't find what I was after so here goes.

SELECT * FROM trees WHERE trees.`title` LIKE  '%elm%'

This works fine, but not if the tree is named Elm or ELM etc...

How do I make SQL case insensitive for this wild-card search?

I'm using MySQL 5 and Apache.

1 Answer

0 votes
by (40.3k points)
edited by

Try the code given below:

SELECT*

FROM trees

WHERE trees.`title` COLLATE UTF8_GENERAL_CI LIKE '%elm%'

If you are adding COLLATE UTF8_GENERAL_CI to your column's definition, then you can make SQL case insensitive.

ALTER TABLE trees 

MODIFY COLUMN title VARCHAR(…) CHARACTER

SET UTF8 COLLATE UTF8_GENERAL_CI.

The above code will also rebuild any indexes on the column hence, they can be used for the queries without leading '%'.

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...