" and the other has " jdoe@aol.co...">
Have Geek Brain

How to match two email fields that have a friendly email address

One table has " John Doe <jdoe@aol.com>

" and the other has " jdoe@aol.com

". Is there a UDF or alternative method that will match the email address from the first field in the second field?

It won't be production code, I just need it for ad-hoc analysis. It is a shame that the DB does not store both friendly and unscrupulous email addresses.

Update: Fixed formatting should be <

and >

on the ground.

+1


a source to share


4 answers


You can make a join using the LOCATE method, something like ...



 SELECT * FROM table1 JOIN table2 ON (LOCATE(table2.real_email, table1.friend_email) > 0)
0


a source


I would strip the email addresses on the last space - that should give you the email address. The exact code will depend on your database, but some crude pseudocode:



email = row.email
parts = email.split(" ")
real_email = parts[ len(parts) - 1 ]
+1


a source


You should be able to use the LIKE keyword depending on how consistent the pattern is for "friendly" email addresses.

SELECT
     T1.nonfriendly_email_address,
     T2.friendly_email_address
FROM
     My_Table T1
INNER JOIN My_Table T2 ON
     T2.friendly_email_address LIKE '%<' + T1.nonfriendly_email_address + '>'
+1


a source


Maybe the following TSQL code can help you:

DECLARE @email varchar(200)
SELECT @email = 'John Doe jdoe@aol.com'

SELECT REVERSE(SUBSTRING(REVERSE(@email), 0,CHARINDEX(' ', REVERSE(@email))))

This operator returns:

jdoe@aol.com

Logically speaking:

  • Reverse email column
  • Find the index of the first character '... everything up to this point is your actual email address
  • Tune the column from the beginning of the (reversed) row to the index found in step 2.
  • Return the string again, putting it in the correct order.

There might be more elegant ways to do this, but it will work and therefore you can use it for one side of your JOIN. This works because email addresses cannot contain spaces, so the last space (or the first one when you cancel it) will be the delimiter between your actual email and the friendly one. As far as I know, TSQL does not contain the LastIndexOf () function, which would be useful to avoid double calls to the Reverse () function.

0


a source






More articles:


All Articles