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 @OutputStringYou 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