how to handle white spaces in sql

Question

I want to write an SQL query that will fetch all the students who live in a specific Post Code. Following is my query.

SELECT * FROM `students` AS ss WHERE ss.`postcode` LIKE 'SE4 1NA';

Now the issue is that in database some records are saved without the white space is postcode, like SE41NA and some may also be in lowercase, like se41na or se4 1na.

The query gives me different results based on how the record is saved. Is there any way in which I can handle this?

Answer 1

Using regexp is one way to do it. This performs a case insensitive match by default.

SELECT * FROM students AS ss 
WHERE ss.postcode REGEXP '^SE4[[:space:]]?1NA$';

[[:space:]]? matches an optional space character.

REGEXP documentation MySQL