使用 tsql 从字符串中提取电子邮件地址 [英] Extract email address from string using tsql

问题描述

我正在尝试从现有评论字段中提取电子邮件地址并将其放入自己的列中.该字符串可能类似于这是一个电子邮件地址为 [e-mail] 的示例评论"，或者只是电子邮件本身[e-mail]".

I'm trying to extract email addresses from an existing comments field and put it into its own column. The string may be something like this "this is an example comment with an email address of [e-mail]" or just literally the email itself "[e-mail]".

我认为最好的办法是找到@"符号的索引并在两个方向上搜索，直到命中字符串的末尾或有一个空格.任何人都可以帮我完成这个实现吗?

I figure the best thing to do would be to find the index of the '@' symbol and search in both directions until either the end of the string was hit or there was a space. Can anyone help me out with this implementation?

推荐答案

您可以在字符串中搜索 '@'.然后你在 '@' 的 LEFT 和 RIGHT 一侧得到字符串.然后，您希望 REVERSE LEFT 侧并获得 ' ' 的第一次出现，然后从那里获取 SUBSTRING .然后 REVERSE 它得到原始形式.相同的原则适用于 RIGHT 侧，无需执行 REVERSE.

You can search for '@' in the string. Then you get the string at the LEFT and RIGHT side of '@'. You then want to REVERSE the LEFT side and get first occurrence of ' ' then get the SUBSTRING from there. Then REVERSE it to get the original form. Same principle apply to the RIGHT side without doing REVERSE.

示例字符串:'some text [e-mail] some text'

1. LEFT = 'some text someemail'
2. RIGHT = '@domain.org 一些文字'
3. 向左反转 = 'liameemos txet emos'
4. SUBSTRING 直到第一个空格 = 'liameemos'
5. REVERSE(4) = someemail
6. SUBSTRING (2) 直到第一个空格 = '@domain.org'
7. 组合 5 和 6 = '[e-mail]'

1. LEFT = 'some text someemail'
2. RIGHT = '@domain.org some text'
3. Reverse LEFT = 'liameemos txet emos'
4. SUBSTRING up to the first space = 'liameemos'
5. REVERSE(4) = someemail
6. SUBSTRING (2) up to the first space = '@domain.org'
7. Combine 5 and 6 = '[e-mail]'

您的查询将是:

;WITH CteEmail(email) AS(
    SELECT '[e-mail]' UNION ALL
    SELECT 'some text [e-mail] some text' UNION ALL
    SELECT 'no email'
)
,CteStrings AS(
    SELECT
        [Left] = LEFT(email, CHARINDEX('@', email, 0) - 1),
        Reverse_Left = REVERSE(LEFT(email, CHARINDEX('@', email, 0) - 1)),
        [Right] = RIGHT(email, CHARINDEX('@', email, 0) + 1)
    FROM CteEmail
    WHERE email LIKE '%@%'
)
SELECT *,
    REVERSE(
        SUBSTRING(Reverse_Left, 0, 
            CASE
                WHEN CHARINDEX(' ', Reverse_Left, 0) = 0 THEN LEN(Reverse_Left) + 1
                ELSE CHARINDEX(' ', Reverse_Left, 0)
            END
        )
    )
    +
    SUBSTRING([Right], 0,
        CASE
            WHEN CHARINDEX(' ', [Right], 0) = 0 THEN LEN([Right]) + 1
            ELSE CHARINDEX(' ', [Right], 0)
        END
    )
FROM CteStrings

示例数据:

email
----------------------------------------
[e-mail]
some text [e-mail] some text
no email

结果

---------------------
[e-mail]
[e-mail]

这篇关于使用 tsql 从字符串中提取电子邮件地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！