andresg3 andresg3 - 6 months ago 14
Bash Question

MySQL join column with blank space

Table a:

phone_number
+123456789000_
+123456789001
+123456789002_
+123456789003
+123456789000


The _ at the end of some phone numbers represents a blank space

Table b:

phone_number
+123456789000
+123456789002


Table b contains the phone_numbers from table a that had a space. The blank space was trimmed.

I need to check if the phone was not added twice on table a(one with a space character and without the space character).

I tried:

SELECT a.phone_number FROM a
JOIN b ON a.phone_number = b.phone_number;


But it returns all existing phone numbers in b. What I wanted to get from the example above is +123456789000

Thanks.

Answer

You should do a self-join instead:

SELECT t1.phone_number 
FROM a AS t1
JOIN a AS t2 ON t1.phone_number = CONCAT(t2.phone_number, '_');

Edit:

The previous query looks for an underscore character in a table. To detect duplicate phone numbers based on trailing space characters you can use this query instead:

SELECT phone_number
FROM a 
GROUP BY RTRIM(phone_number)
HAVING COUNT(*) > 1

Demo here