How to extract multiple strings from single rows in SQL Server

Question

I have e.g. the following table data:

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

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
– Martin Smith, Commented Jan 30, 2011 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
– Daniel, Commented Jan 30, 2011 at 13:32

Mikael Eriksson · Accepted Answer · 2011-01-30 20:39:45Z

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 ([email protected]) and Marta ([email protected]) are doing fine.')
insert into @T values (2, 'Nothing special here')
insert into @T values (3, 'Another email address ([email protected])')

;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
[email protected]
[email protected]
[email protected]

Martin Smith · Accepted Answer · 2011-01-30 22:41:44Z

3

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 ([email protected]) and Marta ([email protected]) are doing fine.'
         UNION ALL
         SELECT 2, 'Nothing special here'
         UNION ALL
         SELECT 3, 'Another email address ([email protected])'),
     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)

answered Jan 30, 2011 at 22:41

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Comments

Tim · Accepted Answer · 2011-01-30 13:36:16Z

-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, 2011 at 13:36

Tim

5,4213 gold badges34 silver badges42 bronze badges

8 Comments

Tim Over a year ago

@Daniel. You need to write a UDF.

Daniel Over a year ago

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 Over a year ago

Not my downvote... but I believe because your answer seemed like not doing it in SQL.

Tim Over a year ago

@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 Over a year ago

@Martin, if that's your downvote then would you care to explain why?

|

Collectives™ on Stack Overflow

How to extract multiple strings from single rows in SQL Server

3 Answers 3

Comments

Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related