RegEx On MySQL Query To Do Left Join

Question

I have this table, named Prospects :

+---------+-------------+
| Name    | PhoneNumber |
+---------+-------------+
|John     | 08199999    |
|Viona    | 08332222    |
+---------+-------------+

on the other hand I have this table too, named SMSD :

+-----------------+---------------+
| Message         | PhoneNumber   |
+-----------------+---------------+
|Hello World!     | +628199999    |
|Hi World         | +628332222    |
+-----------------+---------------+

now I need to do LEFT JOIN to that both tables based on PhoneNumber : Prospects.PhoneNumber = SMSD.PhoneNumber while on the SMSD table phone number always had country code prefix.

thanks!

are the length of the values fixed? eg +628199999 and 08199999 and also the pattern? — John Woo
– John Woo, Commented Feb 1, 2013 at 8:31
the only thing you can use as the pattern is the prefix. always started with +62 on SMSD table. the length of number are dynamic. for some GSM operator has 12 digits number, but other might have 11 or even 10 digits number. — Saint Robson
– Saint Robson, Commented Feb 1, 2013 at 8:33

Peter Herdenborg · Accepted Answer · 2013-02-01 08:36:11Z

2

select * from Prospects
left join SMSD on substring(SMSD.PhoneNumber, 4) = substring(Prospects.PhoneNumber, 2)

(Not tested)

This should remove the country code prefix from the SMSD phone number and the leading zero from the Prospects phone number before performing the join.

answered Feb 1, 2013 at 8:36

Peter Herdenborg

5,9821 gold badge23 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1914530 · Accepted Answer · 2013-02-01 08:43:26Z

0

SELECT *
FROM prospects AS t1
JOIN SMSD AS t2
ON 
CONCAT(@prefix, SUBSTRING(t1.PhoneNumber, 2)) = t2.PhoneNumber
WHERE t1.PhoneNumber LIKE @prefix%

answered Feb 1, 2013 at 8:43

user1914530

Collectives™ on Stack Overflow

RegEx On MySQL Query To Do Left Join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related