CODEWITHSUNDEEP
sqlsql-serverdatabase
naeemgik
naeemgik

Reputation: 2272

How to replace a substring before and after a specific character in SQL Server?

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?.

Upvotes: 1

Views: 1411

Answers (3)

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

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);

Upvotes: 1

Doug_Ivison
Doug_Ivison

Reputation: 650

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.

Upvotes: 1

bummi
bummi

Reputation: 27367

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)

Upvotes: 1

Related Questions