Replace specific string in SQL_问答_开发者_运维开发者技术经验分享

开发者 https://www.devze.com 2023-04-10 20:45 出处：网络

We have开发者_StackOverflow中文版 a MS SQL database that holds HTML markup. However, we need to frequently change the CSS declarations at the top. Is there a way to write a SQL query that would allow

replace '<style>OLDSTYLES</style>' with '<style>NEWSTYLES</style>'

More specifically - can we use a dynamic variable in place of "OLDSTYLES" so that we don't have to copy+paste the old css in each time?

If HTML markup is a well formed XML and you are familiar with XPath, then you might try the following solution. It should work for the case you specified (the style tag), but if you need to modify another tag, you need to change XPath expression accordingly, which here is "(/html/style/text())[1]". For more details: XML data modification with XQuery at MSDN.

-- sample data
declare @data table
(
    html xml
)
insert into @data select '<html><style>oldstyle</style><body></body></html>'

-- solution
declare @replacement varchar(50)
set @replacement = 'newstyle'

update @data
set html.modify('replace value of (/html/style/text())[1] with sql:variable("@replacement")')

select * from @data

This should work fine, it might need a little tweaking but it does the trick:

DECLARE
    @markup NVARCHAR(MAX)
    ,@OldStyles NVARCHAR(MAX)
    ,@NewStyles NVARCHAR(MAX)
    ,@PatIndex INT

SELECT @markup = '<style>OLDSTYLES</style>'
       ,@OldStyles = 'OLDSTYLES'
       ,@NewStyles = 'NewStyles'

SET @PatIndex = PATINDEX('%<style>%',@markup)

IF @PatIndex > 0
SET @markup = REPLACE(@markup,@OldStyles,@NewStyles)

SELECT @markup

Don't hard-code (inline) the styles. Use a stylesheet and class names.

What you want to do is link to an external stylesheet.

If that won't work, then put some sort of keystring (such as "OLDSTYLES") there, and replace it after you take the html out of the database.