Sometime you need to eliminate duplicated char in string. Static replacement like
In that case you can use dynamic replacement. Here is an example:
SET @Output_String=REPLACE(@teststring,'__','++')is good for not big and predictable values. But what if you need to change replaced chars dynamically or its quantity is big enough?
In that case you can use dynamic replacement. Here is an example:
SET @Output_String=REPLACE(@teststring,'__','++')
DECLARE @Duplicate NVARCHAR(100)= '#$'
DECLARE @TestString NVARCHAR(MAX)= 'test_test__f##f2$$g'
DECLARE @Replacement NVARCHAR(MAX)= ''
DECLARE @OutputString NVARCHAR(MAX)= @teststring ;
WITH numbers
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY o.object_id, o2.object_id ) Number
FROM sys.objects o
CROSS JOIN sys.objects o2
),
chars
AS ( SELECT SUBSTRING(@Duplicate, 1, 1) CHAR ,
CAST(1 AS INT) [LEVEL]
UNION ALL
SELECT SUBSTRING(@Duplicate, numbers.Number, 1) CHAR ,
CAST(numbers.Number AS INT) [LEVEL]
FROM numbers
JOIN chars ON chars.Level + 1 = numbers.Number
WHERE LEN(SUBSTRING(@Duplicate, numbers.Number, 1)) > 0
),
Replicated
AS ( SELECT REPLICATE(CHAR, numbers.number) Repl ,
numbers.Number
FROM chars
CROSS JOIN numbers
)
SELECT @OutputString = REPLACE(@OutputString, Repl, @Replacement)
FROM replicated
WHERE number <= LEN(@TestString)
SELECT @OutputString
You can modify @Duplicate string which is storage for replaced characters. Also @Replacements is a string which will be inserted instead of duplicated chars
No comments:
Post a Comment