SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) / Howto udpdate text using Replace html font in textfield / Latest Posts

SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) / Howto udpdate text using Replace html font in textfield / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 13:50:25 GMT20RE: Howto udpdate text using Replace html font in textfieldhttp://www.sqlservercentral.com/Forums/Topic1382389-392-1.aspxhere'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:[quote]--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;','') [/quote][code]Create table #Inventory_Trans (OtherId int,Description varchar(max) )insert into #Inventory_Trans SELECT 1,' <div>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. 'UNION ALL SELECT 2,' <div>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. <div>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. '--now get the ID's using a tally tabledeclare @pre varchar(100),@post varchar(100),@pr int,@po int,@st intset @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 30T1.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 Description2Into #Results --capture into an intemediary tableFROM Tally T1 CROSS APPLY #Inventory_Trans T2 WHERE N <= LEN( T2.Description ) AND SUBSTRING( T2.Description, T1.N, @pr ) = @preSELECT * from #Resultsselect distinct 'UPDATE #Inventory_Trans SET Description = REPLACE(Description,''' + DesiredString + ''','''') 'from #Resultsdrop table #Inventory_Transdrop table #Results[/code]Thu, 08 Nov 2012 06:02:14 GMTLowellRE: Howto udpdate text using Replace html font in textfieldhttp://www.sqlservercentral.com/Forums/Topic1382389-392-1.aspxExtract the data, send it to a web developer, get them to change it, import it back in ?Thu, 08 Nov 2012 05:54:00 GMTmister.magooRE: Howto udpdate text using Replace html font in textfieldhttp://www.sqlservercentral.com/Forums/Topic1382389-392-1.aspx[quote][b]andrea.mayo (11/8/2012)[/b][hr]Could you please perhaps give a little more detail I am new to SQL[/quote]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 developerThu, 08 Nov 2012 05:45:43 GMTEugene ElutinRE: Howto udpdate text using Replace html font in textfieldhttp://www.sqlservercentral.com/Forums/Topic1382389-392-1.aspxCould you please perhaps give a little more detail I am new to SQLThu, 08 Nov 2012 05:22:27 GMTandrea.mayoRE: Howto udpdate text using Replace html font in textfieldhttp://www.sqlservercentral.com/Forums/Topic1382389-392-1.aspxYour best friend here will be CLR function with RegEx.Replace. Thu, 08 Nov 2012 05:20:07 GMTEugene ElutinHowto udpdate text using Replace html font in textfieldhttp://www.sqlservercentral.com/Forums/Topic1382389-392-1.aspxI have a textfield SectionText in column in my table Section. The sectionText field contains multiple instances of htmlwith font references. e.g. <div>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. 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. selectSUBSTRING(SectionText,1,CHARINDEX('"font-family:',SectionText)) + '"font-family: Arial'+ SUBSTRING(SectionText,CHARINDEX(';"',SectionText,CHARINDEX('"font-family:',SectionText)), LEN(SectionText) )from tSectionwhere SectionText like '%"font-family:%;"%' If you can help that would be great!Thu, 08 Nov 2012 04:09:06 GMTandrea.mayo