November 8, 2012 at 4:09 am
I have a textfield SectionText in column in my table Section. The sectionText field contains multiple instances of html
with font references. e.g.
<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;">
I want to write an update statement that replaces all occurrences of font-family: with "font-family: Arial;" i.e. all the html has the same arial font.
I thought I could use the REPLACE function
select replace(SectionText, '%"font-family:%;"%', '"font-family: Arial;"')
from tSection
But you can’t use wildcard characters.
I found the below bit of code on the web, which does work, but it only replaces the first occurrence of the word “font-family”, there could be multiple instances in the same field.
select
SUBSTRING(SectionText,1,CHARINDEX('"font-family:',SectionText))
+ '"font-family: Arial'+
SUBSTRING(SectionText,CHARINDEX(';"',SectionText,CHARINDEX('"font-family:',SectionText)),
LEN(SectionText)
)
from tSection
where SectionText like '%"font-family:%;"%'
If you can help that would be great!
November 8, 2012 at 5:20 am
Your best friend here will be CLR function with RegEx.Replace.
November 8, 2012 at 5:22 am
Could you please perhaps give a little more detail I am new to SQL
November 8, 2012 at 5:45 am
andrea.mayo (11/8/2012)
Could you please perhaps give a little more detail I am new to SQL
You will need to develop this function in .NET (c# or vb.net).
If you don't know any of .NET language, you will need help from .net developer
November 8, 2012 at 5:54 am
Extract the data, send it to a web developer, get them to change it, import it back in ?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
November 8, 2012 at 6:02 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply