SQL join two tables to split email from second table

Question

I have the two following tables. Where the username from table A is the first part of the email address in table B.

How do I join the two tables together by using split? Is there another way to do this?

This is my current query:

    SELECT 
    A.Full_name,
    A.Username,
    B.Email,
    FROM
    A
    LEFT JOIN B
    ON A.Username = B.Email

I would like to use split(B.Email, '@') - however I don't think this syntax is correct.

Table A
--------------------------------
Full_name  | Username          |
--------------------------------
John Doe   | johndoe           |
--------------------------------
Jane Smith | janesmith         |
--------------------------------



Table B
----------------------------------
Full_name  | Email               |
----------------------------------
John Doe   | [email protected]   |
----------------------------------
Jane Smith | [email protected] |
----------------------------------

Answer 1

You can use like operator like this:

SELECT 
A.Full_name,
A.Username,
B.Email,
FROM
A
LEFT JOIN B
ON B.Email like A.Username+'@%'

In this case, the username will have to match exactly with text only upto the @ character.

For SQL Server:

SELECT 
A.Full_name,
A.Username,
B.Email,
FROM
A
LEFT JOIN B
ON Charindex(A.Username+'@',B.Email) = 1

Charindex(A.Username+'@',B.Email) = 1 because it must match from the starting of the email, not anywhere (as the other answer suggest).

Similarly, for Oracle:

SELECT 
A.Full_name,
A.Username,
B.Email,
FROM
A
LEFT JOIN B
ON INSTR(B.Email, A.Username||'@') = 1

Answer 2

To the best of my knowledge, mssql doesn't support a split() function. You can use a combination of left() and charindex() to accomplish your objective.

SELECT 
A.Full_name,
A.Username,
B.Email
FROM
A
LEFT JOIN B
ON A.Username = left(B.Email, charindex(',', B.Email)-1)