Replace specific string in SQL
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 us to easily replace everything between the tags? For example:
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.
加载中,请稍侯......
精彩评论