I want to insert strings before and after a matched string so that, for example, aaa yyy bbb becomes aaa [yyy] bbb
I used to do this with
REPLACE(MyColumn, 'YYY', '[YYY]')
but a disadvantage of this is that if the match is case INsensitive the replacement may be the wrong case - as in this example because aaa yyy bbb would become aaa [YYY] bbb
It would also be helpful, as a refinement, if the Match could be a wildcard (e.g. to match a whole word, rather than partial words).
The replace needs work for multiple occurrences in the string
The code I've come up with seems very clunky, and there must be a better way?
DECLARE @strMatch varchar(100) = 'yyy'
, @strInsBefore varchar(100) = '['
, @strInsAfter varchar(100) = ']'
, @intOffset int = 0
, @intStrLen int -- length of search string (assumed no variable-length wildcards)
, @intStrLenReplaced int
, @intLenReplace int -- total additional length of replacement before/after strings
, @intLoop int = 1
, @intRowCount int = 1 -- Force first iteration
CREATE TABLE #TEST
(
[T_ID] int identity(1, 1) NOT NULL
, T_offset int NULL
, T_Len int NULL
, T_MatchPosition int NULL
, T_RightLen int NULL
, StringOrig varchar(30) NOT NULL
, StringNew varchar(30) NULL
, StringExpected varchar(30) NOT NULL
)
The Test data and Expected Results:
INSERT INTO #test(StringOrig, StringExpected)
SELECT 'xxx yyy zzz', 'xxx [yyy] zzz' -- Embedded
UNION ALL SELECT 'yyy zzz', '[yyy] zzz' -- Leading
UNION ALL SELECT 'xxx yyy', 'xxx [yyy]' -- Trailing
UNION ALL SELECT 'xxx yyy zzz yyy zzz', 'xxx [yyy] zzz [yyy] zzz' -- Multiple Embedded
UNION ALL SELECT 'xxx yyy zzz yyy', 'xxx [yyy] zzz [yyy]' -- Embedded and Trailing
UNION ALL SELECT 'yyy zzz yyy zzz', '[yyy] zzz [yyy] zzz' -- Leading and Embedded
UNION ALL SELECT 'xxx yyyyyy zzz', 'xxx [yyy][yyy] zzz' -- Adjacent matches
UNION ALL SELECT 'xxx zzz', 'xxx zzz' -- No match
-- EDIT: Additional tests:
UNION ALL SELECT 'xxx [yyy] zzz', 'xxx [[yyy]] zzz' -- Already replaced
UNION ALL SELECT 'xxx YyY zzz', 'xxx [YyY] zzz' -- Mixed-Case Test
Assign working values etc.
UPDATE U
SET StringNew = StringOrig -- Copy Original String into New String
-- Assign working values
, T_offset = 1
, T_Len = LEN(StringOrig)
FROM #TEST AS U
SELECT @intStrLen = LEN(@strMatch)
, @intLenReplace = LEN(@strInsBefore) + LEN(@strInsAfter)
, @intStrLenReplaced = @intLenReplace
--DEBUG:
SELECT [@intStrLen]=@intStrLen, [@intLenReplace]=@intLenReplace, [@intStrLenReplaced]=@intStrLenReplaced
Match and Replace loop:
WHILE @intLoop <= 10 AND @intRowCount >= 1
BEGIN
UPDATE U
SET StringNew = CASE WHEN MatchPosition IS NOT NULL
THEN LEFT(StringNew, MatchPosition - 1)
+ @strInsBefore
+ SUBSTRING(StringNew, MatchPosition, @intStrLen)
+ @strInsAfter
+ CASE WHEN RightLen < 1 THEN '' ELSE RIGHT(StringNew, RightLen) END
ELSE StringNew
END
, T_offset = MatchPosition + @intStrLenReplaced
, T_Len = T_Len + @intLenReplace
-- For debugging use only:
, T_MatchPosition = MatchPosition
, T_RightLen = RightLen
FROM #TEST AS U
CROSS APPLY
(
SELECT [MatchPosition] = NullIf(PATINDEX('%' + @strMatch + '%'
, SUBSTRING(StringNew, T_offset, T_Len)
), 0)
+ T_offset - 1
) AS X1
CROSS APPLY
(
SELECT [RightLen] = T_Len - MatchPosition - @intStrLen+1
) AS X2
WHERE StringNew LIKE '%' + @strMatch + '%'
AND T_offset IS NOT NULL
SELECT @intRowCount = @@ROWCOUNT
-- DEBUG:
PRINT CONCAT('Loop=', @intLoop, ', @intRowCount=', @intRowCount)
SELECT [@intLoop]=@intLoop, * FROM #TEST
SELECT @intLoop = @intLoop + 1 -- Probably don't need a finite limit, @intRowCount=0 will do, so this just to stop "runaway" during DEV
END
Results output:
SELECT [Diff] = CASE WHEN StringNew = StringExpected COLLATE Latin1_General_BIN THEN 'OK' ELSE 'Diff' END
, *
FROM #TEST
GO
DROP TABLE #TEST
GO
