Replace variable emails in long sting with blank@blank.com

Question

Post reply

Replace variable emails in long sting with blank@blank.com

aehtra

Grasshopper

Points: 19
More actions
May 14, 2014 at 6:09 pm

#286760

I have a very long string that has many different email addresses in it. I need to replace each email address with blank@blank.com, but this is beyond my string manipulation skills. Any help would be appreciated. Here is a small sample of what the field looks like.
<set type="TomAgingAlertProperties"><TomAgingAlertProperties><properties><USE_AGING_CALENDAR value="true"/><CALENDAR_INT value="206"/><AGE_PRE_PAYMENTS value="true"/><AGE_AS_APPLIED value="true"/><OTHER_LIST value="awnaw@aol.com"/><SEND_TO_OTHER value="true"/><SEND_TO_REGIONALS value="false"/><SEND_TO_AE value="true"/><SEND_FROM_NAME value="shawn@hotmail.com"/><INTERVAL value="60"/><ENABLED value="false"/><STATION_INT value="2"/><ID value="{764C2DFB-B553-4947-BA23-4B46371B4A3E}"/></properties></TomAgingAlertProperties><TomAgingAlertProperties><properties><USE_AGING_CALENDAR alue="true"/><CALENDAR_INT value="206"/><AGE_PRE_PAYMENTS value="true"/><AGE_AS_APPLIED value="true"/><OTHER_LIST value="Shass@gmail.com"/><SEND_TO_OTHER value="true"/><SEND_TO_REGIONALS alue="false"/><SEND_TO_AE value="true"/><SEND_FROM_NAME value="bob@bliptownmail.com"/><INTERVAL value="60"/>
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Maybe the DelimitedSplit8K can help you with this. You could split, change the emails and rejoin the string.

Here's an example:

CREATE TABLE #Test(Somestring varchar(8000))

INSERT #Test VALUES('<set type="TomAgingAlertProperties"><TomAgingAlertProperties><properties><USE_AGING_CALENDAR value="true"/><CALENDAR_INT value="206"/><AGE_PRE_PAYMENTS value="true"/><AGE_AS_APPLIED value="true"/><OTHER_LIST value="awnaw@aol.com"/><SEND_TO_OTHER value="true"/><SEND_TO_REGIONALS value="false"/><SEND_TO_AE value="true"/><SEND_FROM_NAME value="shawn@hotmail.com"/><INTERVAL value="60"/><ENABLED value="false"/><STATION_INT value="2"/><ID value="{764C2DFB-B553-4947-BA23-4B46371B4A3E}"/></properties></TomAgingAlertProperties><TomAgingAlertProperties><properties><USE_AGING_CALENDAR alue="true"/><CALENDAR_INT value="206"/><AGE_PRE_PAYMENTS value="true"/><AGE_AS_APPLIED value="true"/><OTHER_LIST value="Shass@gmail.com"/><SEND_TO_OTHER value="true"/><SEND_TO_REGIONALS alue="false"/><SEND_TO_AE value="true"/><SEND_FROM_NAME value="bob@bliptownmail.com"/><INTERVAL value="60"/>')

SELECT * FROM #Test

SELECT STUFF((SELECT '"' + CASE WHEN Item LIKE '%_@_%._%' THEN 'blank@blank.com' ELSE Item END

FROM #Test t

CROSS APPLY MEXDWREP.dbo.DelimitedSplit8K(Somestring, '"')

ORDER BY ItemNumber

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')

GO

DROP TABLE #Test

You can read about the splitter in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

And about the "join" method in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

NOTE: Remember that you could truncate the strings if they're over 8000 characters long without noticing.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

aehtra Grasshopper Points: 19 More actions · Answer 2

aehtra

Grasshopper

Points: 19

May 20, 2014 at 3:32 pm

#1715161

Thanks for the reply. Anything more simple?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I'm not sure what would you consider simple.

If you're able to store your xml in a table without the xml, you could easily update the table. The problem with this is that there are multiple emails with variable lengths and that's going to give you headaches if you try to attack all at once.

CLR could be an option, but could be considered complicated as well.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Is the data valid XML in reality (the sample isn't very clear)?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

aehtra Grasshopper Points: 19 More actions · Answer 5

mister.magoo (5/20/2014)
Is the data valid XML in reality (the sample isn't very clear)?

I believe so, but I don't know anything about XML. Here's the string after I manually went through and replaced the email addresses.

<set type="TomAgingAlertProperties">

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="true"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="2"/>

<ID value="{764C2DFB-B553-4947-BA23-4B46371B4A3E}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="true"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="1"/>

<ID value="{379F8E32-046B-4BFC-9E36-B7E6EDEAF503}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="true"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="4"/>

<ID value="{B0F88C1B-C86B-4207-AE78-98439DE24D15}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="5"/>

<ID value="{CFC526FB-A886-463D-9434-51CAE5C78C32}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="6"/>

<ID value="{36330B3A-31C0-4B71-B2B5-8D52C2ECBE7B}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="7"/>

<ID value="{9CE5E159-0781-4B7B-ACC9-F7151983FB8C}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="8"/>

<ID value="{1819A91A-0954-4E86-96CC-E87E4C58B55F}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="9"/>

<ID value="{274F3B07-D08A-4434-86E3-4E2C08B75876}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="10"/>

<ID value="{6465D351-66CF-4702-8FA8-E15EFFDA8D25}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="11"/>

<ID value="{FCC69004-F648-421A-B913-F32703222875}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="12"/>

<ID value="{7DADEDC1-6FCF-42C9-97E1-C64539DD0837}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="13"/>

<ID value="{A0D2F060-2D64-4D98-8856-7988AA21573A}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="false"/>

<OTHER_LIST value="blank@blank.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="blank@blank.com"/>

<INTERVAL value="90"/>

<ENABLED value="false"/>

<STATION_INT value="14"/>

<ID value="{5612D249-8FD4-4DB8-B987-26CA917B3658}"/>

</properties>

</TomAgingAlertProperties>

</set>

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

It looks like valid xml, so you could try this, but it's not really simple

declare @sample table(id int identity(1,1) not null primary key,Results varchar(max));

insert @sample(Results)

values('

<set type="TomAgingAlertProperties">

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR value="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="true"/>

<OTHER_LIST value="awnaw@aol.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS value="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="shawn@hotmail.com"/>

<INTERVAL value="60"/>

<ENABLED value="false"/>

<STATION_INT value="2"/>

<ID value="{764C2DFB-B553-4947-BA23-4B46371B4A3E}"/>

</properties>

</TomAgingAlertProperties>

<TomAgingAlertProperties>

<properties>

<USE_AGING_CALENDAR alue="true"/>

<CALENDAR_INT value="206"/>

<AGE_PRE_PAYMENTS value="true"/>

<AGE_AS_APPLIED value="true"/>

<OTHER_LIST value="Shass@gmail.com"/>

<SEND_TO_OTHER value="true"/>

<SEND_TO_REGIONALS alue="false"/>

<SEND_TO_AE value="true"/>

<SEND_FROM_NAME value="bob@bliptownmail.com"/>

<INTERVAL value="60"/>

</properties>

</TomAgingAlertProperties>

</set>')

declare @new_value varchar(100)='blank@blank.com';

select asxml.query('

for $s in set

return

<set type="{$s/@type}">

{

for $t in $s/TomAgingAlertProperties/properties

return

<TomAgingAlertProperties>

<properties>

{

for $f in $t/*

return if (local-name($f)="OTHER_LIST")

then <OTHER_LIST value="{ sql:variable("@new_value") }"/>

else if (local-name($f)="SEND_FROM_NAME")

then <SEND_FROM_NAME value="{ sql:variable("@new_value") }"/>

else $f

}

</properties>

</TomAgingAlertProperties>

}

</set>'

)

from @sample

cross apply (select cast(Results as xml)) x(asxml)

What this does is walk the xml hierarchy looking for OTHER_LIST and SEND_FROM_NAME and replacing their "value" attributes with the required dummy value (held in @new_value).

It is not updating the source data, just selecting a new "clean" version.

IF you wanted to update the source, you could probably do that with an XML .modify update.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

GSquared SSC Guru Points: 260824 More actions · Answer 7

I had to fix the XML by adding the necessary end tags for "set","TomAgingAlertProperties", and "properties", since those were missing from the second set of values. Judging from the first set of values, you probably have those end-tags in the real data.

The best I could come up with for this is:

-- Prep Data

if object_id(N'tempdb..#T') is not null

drop table #T;

create table #T (

ID int identity primary key,

X XML not null);

insert into #T(X)

values ('<set type="TomAgingAlertProperties"><TomAgingAlertProperties><properties><USE_AGING_CALENDAR value="true"/><CALENDAR_INT value="206"/><AGE_PRE_PAYMENTS value="true"/><AGE_AS_APPLIED value="true"/><OTHER_LIST value="awnaw@aol.com"/><SEND_TO_OTHER value="true"/><SEND_TO_REGIONALS value="false"/><SEND_TO_AE value="true"/><SEND_FROM_NAME value="shawn@hotmail.com"/><INTERVAL value="60"/><ENABLED value="false"/><STATION_INT value="2"/><ID value="{764C2DFB-B553-4947-BA23-4B46371B4A3E}"/></properties></TomAgingAlertProperties><TomAgingAlertProperties><properties><USE_AGING_CALENDAR alue="true"/><CALENDAR_INT value="206"/><AGE_PRE_PAYMENTS value="true"/><AGE_AS_APPLIED value="true"/><OTHER_LIST value="Shass@gmail.com"/><SEND_TO_OTHER value="true"/><SEND_TO_REGIONALS alue="false"/><SEND_TO_AE value="true"/><SEND_FROM_NAME value="bob@bliptownmail.com"/><INTERVAL value="60"/></properties></TomAgingAlertProperties></set>');

-- End Prep

-- Query "before" values

select

T.X.query('.').value('(/properties/OTHER_LIST/@value)[1]','varchar(100)')

from

#T

cross apply X.nodes('set/*/properties') T(X);

-- Use loop to modify values

declare

@i int = 1,

@sql varchar(8000) =

'update #T

set X.modify(''replace value of (/set/*/properties/OTHER_LIST/@value)[@i_in] with ("blank@blank.com")'');',

@sql_exec varchar(8000);

while exists

(select 1

from #T

cross apply X.nodes('set/*/properties') T(X)

where T.X.query('.').value('(/properties/OTHER_LIST/@value)[1]','varchar(100)') like '%@%.%' and

T.X.query('.').value('(/properties/OTHER_LIST/@value)[1]','varchar(100)') != 'blank@blank.com')

begin;

set @sql_exec = replace(@sql, '@i_in', cast(@i as varchar(8000)));

exec (@sql_exec);

set @i += 1;

end;

-- Query "after" values

select

T.X.query('.').value('(/properties/OTHER_LIST/@value)[1]','varchar(100)')

from

#T

cross apply X.nodes('set/*/properties') T(X);

You'd need to repeat the loop for each sub-value that can be an e-mail address.

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon