How to extract multiple rows from single rows in SQL Server

Question

I have, for example, the following table data:

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

Now I need to select which returns all the email addresses from my text columns (this is normal for checking parentheses) and returns more than one line if there are multiple column addresses in the text. I know how to extract the first element , but I absolutely do not know how to find the second or more results.

Answer 1

You can use cte recursively to cut lines.

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

Result

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

Answer 2

, -, replace, XML.

WITH basedata(id, [text])
     AS (SELECT 1, 'Peter ([e-mail]) and Marta ([e-mail]) are doing fine.'
         UNION ALL
         SELECT 2, 'Nothing special here'
         UNION ALL
         SELECT 3, 'Another email address ([e-mail])'),
     cte(id, t, x)
     AS (SELECT *,
                CAST('<foo>' + REPLACE(REPLACE([text],'(','<bar>'),')','</bar>') + '</foo>' AS XML)
         FROM   basedata)
SELECT id,
       a.value('.', 'nvarchar(max)') as address
FROM   cte
       CROSS APPLY x.nodes('//foo/bar') as addresses(a) 

Answer 3

. , ( ) + originenceLength. , . @-sign, , , , ( -, char),.