Using T-SQL to replace HTML in table not working

Question

I'm trying to do a replace to get some unwanted content out of our Sitefinity Content blocks. To do this I've created a script to replace a term with an empty string. See for yourself:

DECLARE @SearchText nvarchar(512),
        @ReplaceText nvarchar(512)

SET @SearchText = '%%';
SET @ReplaceText = '';

select * from sf_control_properties where val like @SearchText

update sf_control_properties set val = REPLACE(val, @SearchText, @ReplaceText)

It does find the content when I run the select, but the replace function doesn't seem to be doing much. I have no clue what is going on, can someone spot the error? An example database value that should be replaced is below.

Europe’s universities are increasingly developing partnerships in their research and innovation missions, embracing the “Open Innovation model” of university-business collaboration and seeking to embed this is in sound project management and improved intellectual property management that reflects respective interests.
 The Responsible Partnering Initiative has been developed through close collaboration between EUA, the European Industrial Research Management Association (EIRMA), the European Association of Research and Technology Organisations (EARTO) and the European Network of Knowledge Transfer Offices linked to Universities and Public Research Organisations (ProTon Europe).
 

The origins of the Responsible Partnering Initiative  date back to a major Conference which was held in 2004 which brought together the main stakeholders from universities, industry and public research organisations. As a result of the conference, a handbook based on good practices in university/industry collaborative research was published in 2005, entitled “Responsible Partnering: Joining Forces in a World of Open Innovation”. 
 A guide to better practices for collaborative research and knowledge transfer between science and industry, the Responsible Partnering Handbook was designed to help senior managers in the public and private sectors responsible for the creation, transfer and application of knowledge. It is seen as a useful step in the process of facilitating more regular interactions between European universities and industry, of building trust and establishing mutually beneficial relations, while respecting each others' core objectives.
 

The Responsible Partnering Guidelines cover core areas that are crucial to university/industry collaboration concerning aligning interests, professional training and skills, consortia-building, intellectual property rights and patents and finally, importantly, building lasting relationships in collaborative research. The guidelines have been recognised as a pioneering European initiative in various European Commission Communications on improving knowledge transfer and the management of intellectual property rights, and have been recommended strongly for further implementation by the “Aho Report on Creating an Innovative Europe”. 
 The Responsible Partnering Guidelines is a “living document” that is revised regularly in the light of further evidence of good practices and to address new issues in university-business research collaboration. Revisions have been made through “validation workshops” organised with the support from the European Commission DG Research, to gain critical feedback on their strengths and weaknesses and potential for further development and implementation. Workshop participants were drawn from universities, industry (both large and small business enterprises), public research organisations, knowledge transfer offices and other interested parties. A Special Conference was held in Lisbon, Portugal in December 2007, and the report from the conference is available here . 
 In October 2009 a fully revised 'Responsible Partnering Guidelines' was published which include issues such as State Aid, European Community recommendations on IPR management and the results of the EUA DOC-CAREERS project on university-industry partnerships in doctoral research. The Steering Group for the Responsible Partnering Initiative contributes currently also to the EC-initiated University-Business Forum and promotes dialogue with the European Institute of Technology (EIT). 
 EUA has also proposed to the European Research Area Board (ERAB) that the Responsible Partnering Guidelines should be taken forward as a best practice in the first steps to creating an “Open Innovation Charter” proposed as an ERA Milestone in the ERAB’s recommendations in its “Strategic View of the European Research Area: Preparing Europe for a New Renaissance.

CiucaS · Accepted Answer

CREATE TABLE #Text (c1 varchar(100))

INSERT INTO #Text VALUES ('ASDF') 

INSERT INTO #Text VALUES ('BCDE')

INSERT INTO #Text VALUES ('CDE')


DECLARE @SearchText nvarchar(512),
        @ReplaceText nvarchar(512),
        @SearchReplacement nvarchar(512)

SET @SearchText = '%DF%';
SET @SearchReplacement = 'DF'
SET @ReplaceText = '';

select * from #Text where c1 like @SearchText

update #Text set c1 = REPLACE(c1, @SearchReplacement, @ReplaceText)

REPLACE can't find the text because of the '% %' you have in @SearchText. You could also use SUBSTRING to remove the '%' in @SearchText

Using T-SQL to replace HTML in table not working

Answers (2)

Related Questions