clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228817 fiddles created (16683 in the last week).

CREATE TABLE numbers ( id INT, PRIMARY KEY CLUSTERED (id) ); WITH tens(id) AS (SELECT * FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) x(id)) INSERT INTO numbers (id) SELECT b4.id*1000 + b3.id*100 + b2.id*10 + b1.id FROM tens b4, tens b3, tens b2, tens b1;
10000 rows affected
 hidden batch(es)


CREATE FUNCTION dbo.Translate(@ReplaceTarget NVARCHAR(MAX), @from_chars NVARCHAR(MAX), @to_chars NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @steps INT = LEN('_' + @from_chars + '_') - 2 ; WITH dictionary(id, string_from, string_interim, string_to) AS ( SELECT id, string_from, N'<' + string_from + N'>', string_to FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY string_from ORDER BY id) AS occurence, string_from, string_to FROM numbers CROSS APPLY ( SELECT CAST(SUBSTRING(@from_chars, numbers.id, 1) AS NVARCHAR(5)) AS string_from, CAST(SUBSTRING(@to_chars, numbers.id, 1) AS NVARCHAR(5)) AS string_to ) chars WHERE numbers.id > 0 AND numbers.id <= @steps ) sorted_dictionary WHERE occurence = 1 ) , mapping_sequence(id, string_from, string_to) AS ( SELECT 1, N'<', N'<<>' WHERE @from_chars LIKE N'%<%' UNION ALL SELECT 2, N'>', N'<>>' WHERE @from_chars LIKE N'%>%' UNION ALL SELECT 3, N'<<<>>', N'<<>' WHERE @from_chars LIKE N'%<%' AND @from_chars LIKE N'%>%' UNION ALL SELECT 3 + id, string_from, string_interim FROM dictionary WHERE string_from NOT IN (N'<', N'>') UNION ALL SELECT 3 + @steps + id, string_interim, string_to FROM dictionary ) SELECT @ReplaceTarget = REPLACE(@ReplaceTarget, string_from, string_to) FROM mapping_sequence ORDER BY id ; RETURN @ReplaceTarget; END
 hidden batch(es)


WITH tests(id, replace_string, from_chars, to_chars) AS ( SELECT 1, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'a[]b cz', N'b][c-da' UNION ALL SELECT 2, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'a][b cz', N'b[]c-da' UNION ALL SELECT 3, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'abc z', N'bcd- ' UNION ALL SELECT 4, N'abc\defghijk lmnop qrstuvw\x\xyz\\[]', N'abc ', N'bcd-' UNION ALL SELECT 5, N'abc', N'aabbcc', N'b-c-d-' ) SELECT 0 AS mode, *, dbo.Translate(replace_string, from_chars, to_chars) FROM tests UNION ALL SELECT 1 AS mode, *, TRANSLATE(replace_string, from_chars, to_chars) FROM tests ORDER BY id, mode ;
mode id replace_string from_chars to_chars (No column name)
0 1 abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd a[]b cz b][c-da bcd]defghijklm]nop-qr-stuvw][x[]xya][]a[][bsd
1 1 abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd a[]b cz b][c-da bcd]defghijklm]nop-qr-stuvw][x[]xya][]a[][bsd
0 2 abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd a][b cz b[]c-da bcd]defghijklm]nop-qr-stuvw][x[]xya][]a[][bsd
1 2 abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd a][b cz b[]c-da bcd]defghijklm]nop-qr-stuvw][x[]xya][]a[][bsd
0 3 abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd abc z bcd- bcd[defghijklm[nop-qr-stuvw[]x][xy [][ ][]bsd
1 3 abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd abc z bcd- bcd[defghijklm[nop-qr-stuvw[]x][xy [][ ][]bsd
0 4 abc\defghijk lmnop qrstuvw\x\xyz\\[] abc bcd- bcd\defghijk-lmnop-qrstuvw\x\xyz\\[]
1 4 abc\defghijk lmnop qrstuvw\x\xyz\\[] abc bcd- bcd\defghijk-lmnop-qrstuvw\x\xyz\\[]
0 5 abc aabbcc b-c-d- bcd
1 5 abc aabbcc b-c-d- bcd
 hidden batch(es)


WITH tests(id, replace_string, from_chars, to_chars) AS ( SELECT 1, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'xyz', N'ab' ) SELECT *, TRANSLATE(replace_string, from_chars, to_chars) FROM tests ;
Msg 9828 Level 16 State 3 Line 1 The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.
 hidden batch(es)


WITH tests(id, replace_string, from_chars, to_chars) AS ( SELECT 1, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'xyz', N'ab' ) SELECT *, dbo.translate(replace_string, from_chars, to_chars) FROM tests ;
id replace_string from_chars to_chars (No column name)
1 abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd xyz ab abc[defghijklm[nop qr stuvw[]a][ab[][][]asd
 hidden batch(es)


WITH tests(id, replace_string, from_chars, to_chars) AS ( SELECT 1, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'abc', N'abcd' ) SELECT *, TRANSLATE(replace_string, from_chars, to_chars) FROM tests ;
Msg 9828 Level 16 State 3 Line 1 The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.
 hidden batch(es)


WITH tests(id, replace_string, from_chars, to_chars) AS ( SELECT 1, N'abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd', N'abc', N'abcd' ) SELECT *, dbo.translate(replace_string, from_chars, to_chars) FROM tests ;
id replace_string from_chars to_chars (No column name)
1 abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd abc abcd abc[defghijklm[nop qr stuvw[]x][xyz[][z][]asd
 hidden batch(es)