SQL连接两个表，从第二个表拆分电子邮件

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

我有以下两张桌子。表A中的用户名是表B中电子邮件地址的第一部分。

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

如何使用split将两个表连接在一起?还有别的办法吗?

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.

我想用split(B)。但我不认为这种句法是正确的。

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



Table B
----------------------------------
Full_name  | Email               |
----------------------------------
John Doe   | johndoe@yahoo.com   |
----------------------------------
Jane Smith | janesmith@yahoo.com |
----------------------------------

4 个解决方案

#1

You can use like operator like this:

你可以像这样使用like操作符:

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:

SQL服务器:

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).

索引字符(A.Username+'@'，B.Email) = 1，因为它必须从电子邮件开始就匹配，而不是任何地方(如另一个答案所示)。

Similarly, for Oracle:

同样,对甲骨文:

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

#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.

据我所知，mssql不支持split()函数。您可以使用left()和charindex()的组合来实现您的目标。

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

#3

Use LIKE operator instead of =

使用LIKE操作符代替=

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

#4

Why don't you join on Full_name?

你为什么不加入Full_name呢?

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

#1