How to handle spaces in sql

Question

I want to write an SQL query that will retrieve all students who live in a particular Postal Code. Below is my request.

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

Now the problem is that in the database some records are stored without a space - this is a zip code, for example SE41NA, and some can also be lowercase, for example SE41NAor se4 1na.

The query gives me different results based on saving the record. Is there any way I can handle this?

Answer 1

Use regexpis one way to do this. By default, a case-insensitive match is performed.

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

[[:space:]]? .

REGEXP MySQL

Answer 2

, // /. , :

WHERE UPPER(ss.postcode) LIKE 'SE4%1NA'

% , . , , .

, , . , .

Answer 3

UPPER REPLACE.

SELECT * 
FROM students s 
WHERE UPPER(REPLACE(s.postcode, ' ', '')) LIKE '%SE41NA%'

Answer 4

SELECT * 
  FROM students AS ss 
 WHERE UPPER(ss.postcode) LIKE SELECT REPLACE(UPPER('SE4 1NA'), ' ', '%'); ;

, '%'. LIKE

Answer 5

SELECT * FROM students AS ss 
  WHERE UPPER(REPLACE(ss.postcode, ' ', '')) = 'SE41NA' ;