here's a TSQL method, which generates the actual REPLACE statements you would want to use to remove the css style assignment, if it exists.
Notice how i'm generating a lsit of "DesiredString", which is found within the data, and then use that as the basis for creating a suite of REPLACE commands:
--Results:
UPDATE #Inventory_Trans SET Description = REPLACE(Description,'font-family: Arial;','')
UPDATE #Inventory_Trans SET Description = REPLACE(Description,'font-family: Courier New ;','')
UPDATE #Inventory_Trans SET Description = REPLACE(Description,'font-family: Times Roman;','')
UPDATE #Inventory_Trans SET Description = REPLACE(Description,'font-family: Verdana;','')
Create table #Inventory_Trans (OtherId int,Description varchar(max) )
insert into #Inventory_Trans
SELECT 1,'<span style="font-family: Times Roman;"> <span> </span></span><div><span><span style="font-family: Times Roman;">Below is a list of industry regulators that need to be notified when an incident occurs. The regulators may make arrangements to visit the site to assess any potential problems in the short, medium and long term.<br /><br /><strong> </strong></span></span><span><span style="font-size: 18pt;"><span style="color: rgb(54, 95, 145);"><span><span style="font-size: 12pt;"><span style="font-family: Arial;"><span style="font-family: Verdana;">'
UNION ALL
SELECT 2,'<span style="font-family: Times Roman;"> <span> </span></span><div><span><span style="font-family: Times Roman;">Below is a list of industry regulators that need to be notified when an incident occurs. The regulators may make arrangements to visit the site to assess any potential problems in the short, medium and long term.<br /><br /><strong> </strong></span></span><span><span style="font-size: 18pt;"><span style="color: rgb(54, 95, 145);"><span><span style="font-size: 12pt;"><span style="font-family: Arial;"><span style="font-family: Times Roman;"> <span> </span></span><div><span><span style="font-family: Times Roman;">Below is a list of <span style="font-family: Courier New ;">industry regulators that need to be notified when an incident occurs. The regulators may make arrangements to visit the site to assess any potential problems in the short, medium and long term.<br /><br /><strong> </strong></span></span><span><span style="font-size: 18pt;"><span style="color: rgb(54, 95, 145);"><span><span style="font-size: 12pt;"><span style="font-family: Arial;">'
--now get the ID's using a tally table
declare
@pre varchar(100),
@post varchar(100),
@pr int,
@po int,
@st int
set @pre = 'font-family:'
set @post = ';'
set @pr = len(@pre)
set @po = len(@post)
set @st = 1
;WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
SELECT top 30
T1.N,
T2.*,
@pre +
SUBSTRING( T2.Description,
( CASE T1.N WHEN 1 THEN 1 ELSE T1.N + @pr END ),
( CASE CHARINDEX( @post, T2.Description, T1.N + @pr ) WHEN 0 THEN LEN( T2.Description ) - T1.N + @pr
ELSE CHARINDEX( @post, T2.Description, T1.N + @pr ) - ( T1.N + @pr ) END ) )
+ @post AS DesiredString,
Description as Description2
Into #Results --capture into an intemediary table
FROM Tally T1
CROSS APPLY #Inventory_Trans T2
WHERE N <= LEN( T2.Description )
AND SUBSTRING( T2.Description, T1.N, @pr ) = @pre
SELECT * from #Results
select distinct 'UPDATE #Inventory_Trans SET Description = REPLACE(Description,''' + DesiredString + ''','''') '
from #Results
drop table #Inventory_Trans
drop table #Results
Lowell