CODEWITHSUNDEEP

Reputation: 2184

How can I join and group two tables by a column that may or may not have a known prefix?

I have two tables with phone numbers, these numbers may or may not have a + prefixed on them. I have to magically join these two tables!

What are my options if I want to avoid doing a nested select clause to concatenate the data with + where necessary?

Table A has a phone field. Numbers may or may not have a +.
Table B has a phone field. Numbers may or may not have a +.
Numbers with or without a + are considered identical.

Upvotes: 1

Views: 49

Answers (1)

Reputation: 3179

You can go two ways

REPLACE
REPLACE(phone,'+','')
if the length of numbers are constant - SUBSTRING
SUBSTRING(phone, (-1)*@length)

Where @length in the length of your number

So, the join will look like
A JOIN B ON REPLACE(A.phone,'+','') = REPLACE(B.phone,'+','')
or
A JOIN B ON SUBSTRING(A.phone, @length) = SUBSTRING(B.phone, @length)

Upvotes: 2

Related Questions