I am trying to replace the entire string of a column in SQL Server. I do not know what the exact string is. Thus, I have been unsuccessful in using "REPLACE".
CREATE PROCEDURE insertHTML
@html_section nvarchar(50),
@html_content nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON;
UPDATE html
SET html_content = REPLACE(html_content, '', @html_content)
WHERE html_section = @html_section
END
If you want to replace the entire string, just perform UPDATE like this:
go
create procedure insertHTML
@html_section nvarchar(50),
@html_content nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON;
UPDATE html SET html_content = @html_content
WHERE html_section = @html_section
END
The replace may work but will be slower:
SET html_content = REPLACE(html_content, html_content, @html_content)
and this is a kind of overkill as you just need to change a value.
Replace is substitution of character you defined. Query above looks for value '' then replaced by your @html_content. Since you want to replace whole column value, why not use update?
You'll probably need to give us more to go on. Regardless, I'm guessing the HTML section might be something like HEAD or BODY. In that case, you can write a statement more like this:
CREATE PROCEDURE dbo.insertHTML
@html_section nvarchar(50),
@html_content nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON;
UPDATE html SET html_content =
STUFF(html_content,
CHARINDEX('<' + @html_section, html_content), -- opening tag
CHARINDEX('</' + @html_section, html_content) -- closing tag
- CHARINDEX('<' + @html_section, html_content) -- opening tag position
+ LEN('</' + @html_section + '>') -- allow for the length of the closing tag
@html_content)
WHERE html_section = @html_section;
END;
That should find the opening and closing tags for the section and replace them with the contents that were passed as a parameter. I don't know if your incoming @html_content contains the tags or not. If it doesn't, you'd need to change the positiions that are calculated.
