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

Question

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 : Johns@email.com and johns@email.com

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

Example : Johns@email.com and JohnS@email.com

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.

Possible duplicate of ["Like" operator in inner join in SQL](https://stackoverflow.com/questions/23276344/like-operator-in-inner-join-in-sql) — Amira Bedhiafi, Oct 12 '19 at 18:54

Gordon Linoff · Accepted Answer · 2019-10-12T18:54:06.390

2

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);

edited Oct 12 '19 at 18:54

answered Oct 12 '19 at 18:52

Gordon Linoff

1,242,037
58
646
786

Thanks, so using LOWER will convert the emails into lower case while checking to display the results I believe? Plus, If I'm to use the UPDATE method you've suggested, where do I put it in my code? Also, what is the difference between this and using "COLLATE Latin1_General_CI" at the end of the JOIN statement? It does not work sometimes. – Aranorde Oct 12 '19 at 19:01
@Aranorde . . . To use `collate` you have to explicitly state the collation -- which may get complicated if you are mixing collations. `lower()` is a simpler solution. If you want to fix the data, it should be part of the process, if the data is being updated. If the data is static, you can fix the data at any time. – Gordon Linoff Oct 12 '19 at 23:17

Himanshu · Answer 2 · 2019-10-12T18:55:09.750

0

You need to make use of lower or upper functions to match the attributes

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

edited Oct 12 '19 at 18:55

answered Oct 12 '19 at 18:53

Himanshu

3,830
2
10
29

score 0 · Answer 3 · answered Oct 12 '19 at 19:03

You need to convert connected fields both to lower or uppercase on join clause.

SELECT Userid.email, Userid.UserName, Userdetails.CustomerName, Userdetails.CustomerAddress
FROM Userid
JOIN Userdetails on LOWER(Userdetails.CustomerEmail) = LOWER(Userid.Email)

Here is a link for fiddle - http://sqlfiddle.com/#!9/90fc2c/6

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

3 Answers3