How to extract multiple strings from single rows in SQL Server

Question

I have e.g. the following table data:

id    |    text
--------------------------------------------------------------------------------
1     |  Peter (Peter@peter.de) and Marta (marty@gmail.com) are doing fine.
2     |  Nothing special here
3     |  Another email address (me@my.com)

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.

Can we assume that there are absolutely no unbalanced parentheses in the data? — Martin Smith, Jan 30 '11 at 13:29
Yes. I can work the errors out later, but need a working example here to go on from there. It is not email addresses at all in my real world problem, this is just to illustrate the problem and to create a small example. — Daniel, Jan 30 '11 at 13:32

score 7 · Accepted Answer · answered Jan 30 '11 at 20:39

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 (Peter@peter.de) and Marta (marty@gmail.com) are doing fine.')
insert into @T values (2, 'Nothing special here')
insert into @T values (3, 'Another email address (me@my.com)')

;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
Peter@peter.de
me@my.com
marty@gmail.com

score 3 · Answer 2 · answered Jan 30 '11 at 22:41

This assumes there are no rogue parentheses and you would need to add some additional replaces in if your text can contain any XML entity characters.

WITH basedata(id, [text])
     AS (SELECT 1, 'Peter (Peter@peter.de) and Marta (marty@gmail.com) are doing fine.'
         UNION ALL
         SELECT 2, 'Nothing special here'
         UNION ALL
         SELECT 3, 'Another email address (me@my.com)'),
     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)

score -2 · Answer 3 · answered Jan 30 '11 at 13:36

-2

THe substring functions have starting position parameter. So you find the first occurrence,and start the next search (in your loop) at the occurrence position + occurenceLength. You'd need to write a function that returns the values either as a delimited string or table. Use the @-sign to find your way into the email address, and then scan backwards and forwards until you reach white space or a character that's invalid in an email address (or the start-pos or the beginning or the last char).

answered Jan 30 '11 at 13:36

Tim

5,371
3
32
41

@Daniel. You need to write a UDF. – Tim Jan 30 '11 at 13:39
Everytime I have to get close on an SQL Server I begin to hate it after 5 minutes... but okey. Can I write an UDF in a database where I only have read-only access? Like... in a temporay table space? Could you give an example how to write an UDF that returns zero-to-many-rows from a single input row? – Daniel Jan 30 '11 at 13:42
Not my downvote... but I believe because your answer seemed like not doing it in SQL. – Daniel Jan 30 '11 at 13:42
@Daniel: for returning many values read up on functions that return type of "table" in SQL Server Books Online. If you don't have the necessary object-creation privileges then you can't do it. – Tim Jan 30 '11 at 13:44
@Martin, if that's your downvote then would you care to explain why? – Tim Jan 30 '11 at 13:46
@Tim - Just read this comments thread. Are you addressing me in the comment above? If so then nothing to do with me! – Martin Smith Jan 30 '11 at 22:58
@Martin, I thought perhaps you had taken issue with my choice of the @-sign as the delimeter for zeroing-in on the email address. It must have been a "drive by" downvote. – Tim Jan 31 '11 at 12:30
Downvoting because there wasn't much effort put into this answer (there was more effort put into comments showing concern about the downvote). An example or related links would have been helpful. More info: [answer] – ashleedawg May 17 '19 at 04:38

How to extract multiple strings from single rows in SQL Server

3 Answers3

Linked