How to join 2 fields of same data that are not case sensitive

I want to join two tables using an email field.

In certain cases the first letter of the email is upper case and the joining table might have lower case values.

• For an example : [e-mail] and [e-mail]

And in some cases the letter that represents the middle name might have upper case letters too.

• Example : [e-mail] and [e-mail]

If I’m joining the tables with JOIN, it is picking up only the exact email which contains the same case-sensitive letters. I want the join to bypass this and match the emails properly.

SELECT Userid.email, Userid.UserName, Userdetails.CustomerName, Userdetails.CustomerAddress
FROM Userid
JOIN Userdetails on Userdetails.Customer = Userid.Email

The above code is resulting redundant email addresses and I want the JOIN to bypass the exact character check or case-sensitive check.

I’m not using MySQL or anything, using an eCommerce DB analytics tool.

Not an expert, learning SQL on my own at the moment, any help is appreciated.

Answer

Some databases support collation functionality where you can change how the comparisons are done. But the more general solution is to use lower() or upper():

SELECT u.email, u.UserName, ud.CustomerName, ud.CustomerAddress
FROM Userid u JOIN
     Userdetails ud
     ON LOWER(ud.Customer) = LOWER(u.Email);

Note: The use of a function in the on clause will generally impede performance. I would recommend that you change the collation on your database so comparisons are case-insensitive. Or change the data so it is all one case:

update userid
    set email = lower(email)
    where email <> lower(email);