question

I hope that I understand requirements correctly. The values of LastStatus column of the Subscriptions table in report server DB may have some email addresses separated by semicolon if there are multiple addresses. It is also possible that there are no email addresses in the value at all. In this case, we are not interested in these subscription records. While it is possible to use PATINDEX to try to extract all email addresses from the given string, it is not a trivial task. Instead, I will use the following technique: each LastStatus value will have all semicolons replaced with single space and then all spaces are replaced with ` `. This way, by adding ` ` to the head and `<\r>` to the tail, you can get each LastStatus value to be split into nodes named **r** where each node will have a value equal to the value of a single "word" in it. Then it will be possible to use cross apply to first segregate the words into records and then pick only those which look somewhat like email address. I will not use the actual Subscriptions table of the report server, but will use the table variable instead. Form the mockup data, expected results are as follows: - bozo.theclown@wringley.com is extracted from the first record - a@b.com and z@x.co are extracted from the second (@Oleg is disregarded because the email address is not valid - nothing is extracted from the third record - justemail@somewhere.com is extracted from the last record (the whole thing)

-- mockup test data
declare @subscriptions table
(
    SubscriptionID uniqueidentifier not null, 
    LastStatus nvarchar(260) null
);

-- insert test records
insert into @subscriptions
select newid(), 'bozo.theclown@wringley.com looking for more info';

insert into @subscriptions
select newid(), 'send email to @Oleg;a@b.com;z@x.co; please';

insert into @subscriptions
select newid(), null;

insert into @subscriptions
select newid(), 'justemail@somewhere.com';

-- here is the final select (uncomment commented line and modify as needed)
with records (SubscriptionID, LastStatus) as
(
    select 
        SubscriptionID, 
        cast('
    
     ' +  replace(replace(LastStatus, ';', ' '), ' ', '
    
    
     ') + 
        '
    ' as xml) LastStatus
        from @subscriptions
)
    --insert into some_db..some_schema.some_table (some_column_list)
    select
        records.SubscriptionID, x.LastStatus
        from records cross apply
        (
            select 
                item.value('text()[1]', 'varchar(50)') LastStatus
                from records.LastStatus.nodes('//r') R(item)
        ) x
        where
            x.LastStatus like '_%@_%._%';
    
-- The above returns something like this for data above:
SubscriptionID                       LastStatus
------------------------------------ --------------------------
686F0498-2BD6-4032-84A9-53AFCC60DF8C bozo.theclown@wringley.com
673DD0B4-1F41-4417-9709-79863BB32089 a@b.com
673DD0B4-1F41-4417-9709-79863BB32089 z@x.co
2DE37EA7-5243-4F48-B351-BB424B40A99F justemail@somewhere.com

Oleg

with records (SubscriptionID, LastStatus, 
    ReportName, ModifiedDate, LastRunTime) as
(
    select
        s.SubscriptionID, 
        cast('' + replace(replace(s.LastStatus, ';', ' '), 
        ' ', '') + '' as xml) LastStatus, cat.ReportName, 
        s.ModifiedDate, s.LastRunTime
        from dbo.Subscriptions s inner join dbo.Catalog cat
            on s.Report_OID = cat.ItemID
)
    insert into DMReport.dbo.TBL_ManageSubscriptions
    (
        SubscriptionID, LastStatus,
        ReportName, ModifiedDate, LastRunTime
    )
    select
        records.SubscriptionID, x.LastStatus, records.ReportName,
        records.ModifiedDate, records.LastRunTime
        from records cross apply
        (
            select 
                item.value('text()[1]', 'varchar(50)') LastStatus
                from records.LastStatus.nodes('//r') R(item)
        ) x
        where
            x.LastStatus like '_%@_%._%';