如何从 SQL Server 中的单行中提取多个字符串 [英] How to extract multiple strings from single rows in SQL Server

问题描述

我有例如下表数据:

id    |    text
--------------------------------------------------------------------------------
1     |  Peter ([e-mail]) and Marta ([e-mail]) are doing fine.
2     |  Nothing special here
3     |  Another email address ([e-mail])

现在我需要一个 select 来返回我的文本列中的所有电子邮件地址(只需检查括号就可以了)，如果文本中有多个地址，它会返回多行柱子.我知道如何提取第一个元素，但我完全不知道如何提取找到第二个和更多的结果.

Now I need a select that returns all email addresses from my text columns (its okay to just check for the parentheses), and that returns more than one row if there are multiple addresses in the text column. I know how to extract the first element, but am totally clueless about how to find the second and more results.

推荐答案

您可以递归地使用 cte 去除字符串.

You can use a cte recursively to strip out the strings.

declare @T table (id int, [text] nvarchar(max))

insert into @T values (1, 'Peter ([e-mail]) and Marta ([e-mail]) are doing fine.')
insert into @T values (2, 'Nothing special here')
insert into @T values (3, 'Another email address ([e-mail])')

;with cte([text], email)
as
(
    select
        right([text], len([text]) - charindex(')', [text], 0)),
        substring([text], charindex('(', [text], 0) + 1, charindex(')', [text], 0) - charindex('(', [text], 0) - 1) 
    from @T
    where charindex('(', [text], 0) > 0
    union all
    select
        right([text], len([text]) - charindex(')', [text], 0)),
        substring([text], charindex('(', [text], 0) + 1, charindex(')', [text], 0) - charindex('(', [text], 0) - 1) 
    from cte
    where charindex('(', [text], 0) > 0
)
select email
from cte

结果

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

这篇关于如何从 SQL Server 中的单行中提取多个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！