I have a SQL variable:
SET @TSQL = 'this is test string [this text may vary] other text'
Now I want to replace the sub-string "[this text is may vary]" with my own different text. Can anybody help me? Remember the the sub-string which I want to replace is not static it is dynamic and may vary.
This looks similar to my problem but it is only for before specific character. I need for before and after both characters.
How do I replace a substring of a string before a specific character?.
Take the Substring before '[' add your replacement and the Substring right of ']'
declare @TSQL varchar(100)
declare @R varchar(100)
SET @TSQL = 'this is test string [this text may vary] other text'
SET @R = 'MySubstitute'
Select Left(@TSQL,Charindex('[',@TSQL)-1) + @R + RIGHT(@TSQL,Charindex(']',REVERSE(@TSQL))-1)
Is it just as simple as this?
SET @TSQL = 'this is test string ' + @NewText + ' other text'
Or, if the expected-preceding-text is not the only text preceding, maybe:
SET @TSQL = 'this is test string [this text may vary] other text'
DECLARE INT @TSQL_PrefixEnding = PATINDEX('%this is test string [[]%[]] other text%', @TSQL) + LEN('this is test string [') - 1
DECLARE INT @TSQL_SuffixStart = CHARINDEX('] other text', @TSQL, @TSQL_PrefixEnding)
SET @TSQL = LEFT(@TSQL, @TSQL_PrefixEnding ) + @NewText + SUBSTRING(@TSQL, @TSQL_SuffixStart, LEN(@TSQL) - @TSQL_SuffixStart + 1)
(Note: I'd have to test it, to see if the "+1" is necessary or not... but it's just a common kind of adjustment I've seen, in string-length calculation.)
Notes re' answer & edits:
-- My answer was written as if 'this is test string ', etc. were strings to recognize.
-- Patindex (replacing Charindex) means you ONLY recognize the prefix-string, when the suffix-string is also present.
-- I added [ to the string before, and ] to the string after, (wherever they occur) based on your later comment, that had it sound like the brackets are actually part of the string to recognize.
-- The [ is itself enclosed, in [ and ], to "escape" it -- so it will be interpreted literally.
STUFF does this nicely.
declare @string [nvarchar](max) = N'This is a boilerplate string where [this text may vary] but this text should stay'
, @replace_me [nvarchar](max) = N'[this text may vary]'
, @replace_with [nvarchar](max) = N'cool new stuff!';
select stuff (@string
, charindex(@replace_me
, @string)
, len(@replace_me)
, @replace_with);
