sql replace all characters between two strings

Question

I am trying to figure out a way to update / replace only text between two strings.

For instance I need to be able to update the field and replace only what's in between the following script tags leaving the text before the opening script tag untouched.

I want to keep this string<script type="text/javascript" language="javascript">********</script>

I suppose REPLACE is not going to work as the text in between the script tags will vary. Is there some type of wildcard?

UPDATE Products_Joined 
SET TechSpecs = REPLACE (CAST(TechSpecs AS NVARCHAR(MAX)), '<script type="text/javascript" language="javascript">********</script>', '<script type="text/javascript" language="javascript">new text</script>' )

UPDATED: with @Parkyprg answer This works but doesn't replace the closing </script> tag. I end up with this.

I want to keep this string new text</script>

How do we remove the closing script tag as well?

UPDATE Products_Joined
SET TechSpecs = REPLACE(CAST(TechSpecs AS NVARCHAR(MAX)), 
SUBSTRING(CAST(TechSpecs AS NVARCHAR(MAX)),
CHARINDEX('<script type="text/javascript" language="javascript">',TechSpecs),
CHARINDEX('</script>',CAST(TechSpecs AS NVARCHAR(MAX))) - 
CHARINDEX('<script type="text/javascript" language="javascript">',TechSpecs)
),' new text') 

Wodge

Check this and this. – Semiconscious

How does nvarchar(max) in your code correlate with the sql-server-2000 tag that your question's got? SQL Server 2000 doesn't support the max size specifier for strings. – Sapwood

Perhaps it isn't 2000, not sure really. – Wodge

@Wodge - You can use SELECT @@VERSION to find out. For future questions best to say the correct version as 2000 has lots of limitations compared to 2005 and 2008 so you may well get sub optimal answers. – Heteromerous

hmm, tried that but it returned nothing, no error either. – Wodge

@Wodge - OK. Well you must be on at least 2005 as nvarchar(max) works. If you execute DECLARE @I INT = 1 do you get an error? If not you are on 2008. 2005 would give you the error Cannot assign a default value to a local variable. – Heteromerous

Running that I do get "Cannot assign a default value to a local variable." – Wodge

Answer 1

DECLARE @OpenTag varchar(100)
SET @OpenTag = '<script type="text/javascript" language="javascript">'

UPDATE Products_Joined
SET TechSpecs  = STUFF(TechSpecs ,
                       CHARINDEX(@OpenTag, TechSpecs ,1)  + LEN(@OpenTag),
                            CHARINDEX('</script>',TechSpecs ,1)-(CHARINDEX(@OpenTag, TechSpecs ,1)  + LEN(@OpenTag)), 
                       'New Text')

Heteromerous

This won't run on SQL Server 2000 – Applique

@Wodge - Yes. That goes back to Sybase days. Define "Not working". You get an error? It doesn't update anything? Works fine for me. – Heteromerous

"Derived table 'derived' is not updatable because a column of the derived table is derived or constant." on SQL Server 2000 – Udall

@MikaelEriksson - Ah thanks. Will just abandon the derived table and inline everything then. Was only there to avoid repeating the expression. I notice the OP references nvarchar(max) in the question though anyway(!?) – Heteromerous

Your edit works perfect. Just needed to cast the TechSpecs like this CAST(TechSpecs AS NVARCHAR(MAX)) and it worked great. – Wodge

@Wodge - So this is not SQL Server 2000 but the data type is text? – Udall

Really not sure, limited knowledge, VARCHAR(MAX)) works as well – Wodge

Answer 2

It may not be the best solution, but...

UPDATE Products_Joined
SET TechSpecs = REPLACE(TechSpecs, 
SUBSTRING(TechSpecs,
  CHARINDEX('<script type="text/javascript" language="javascript">',TechSpecs),
  CHARINDEX('</script>',TechSpecs) - 
  CHARINDEX('<script type="text/javascript" language="javascript">',TechSpecs)
)
FROM MyTable

Undertrick

You need to add in what is being replaced - at the moment, the REPLACE function only takes two arguments and is missing a ). – Disenthrone

Ok this kinda works with a little modification but doesn't replace the closing </script> tag – Wodge