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. 1228811 fiddles created (16674 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.tvfTranslate( @string NVARCHAR(MAX), @characters NVARCHAR(MAX), @translations NVARCHAR(MAX) ) RETURNS TABLE AS RETURN ( SELECT newString = ( SELECT CASE WHEN replace_from.position > 0 THEN replace_with.string ELSE string.current_char END FROM numbers CROSS APPLY (SELECT SUBSTRING(@string, numbers.id, 1) ) string(current_char) CROSS APPLY (SELECT CHARINDEX(string.current_char, @characters) ) replace_from(position) CROSS APPLY (SELECT SUBSTRING(@translations, replace_from.position, 1)) replace_with(string) WHERE numbers.id > 0 AND numbers.id <= LEN('_' + @characters + '_') - 2 ORDER BY numbers.id FOR XML PATH(''), TYPE ).value('(text())[1]', 'varchar(8000)') )
 hidden batch(es)


SELECT * FROM ( VALUES (1, N'abc', N'abc', N'bcd') ,(2, N'abc', N'abc', N'bc' ) ,(3, N'abc', N'aba', N'bab') ,(4, N'abc', N'aba', N'bad') ,(5, N'abc', N'ab', N'bad') ) test(id, string, characters, translations) CROSS APPLY dbo.tvfTranslate(string, characters, translations) tvf
id string characters translations newString
1 abc abc bcd bcd
2 abc abc bc bc
3 abc aba bab bac
4 abc aba bad bac
5 abc ab bad ba
 hidden batch(es)


CREATE FUNCTION dbo.Translate( @string NVARCHAR(MAX), @characters NVARCHAR(MAX), @translations NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN WITH dictionary AS ( SELECT id, string_from, string_to FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY string_from ) AS id, ROW_NUMBER() OVER (PARTITION BY string_from ORDER BY id) AS occurence, string_from, string_to FROM numbers CROSS APPLY ( SELECT CAST(SUBSTRING(@characters, numbers.id, 1) AS NVARCHAR(5)) AS string_from, CAST(SUBSTRING(@translations, numbers.id, 1) AS NVARCHAR(5)) AS string_to ) chars WHERE numbers.id > 0 AND numbers.id <= LEN('_' + @characters + '_') - 2 ) sorted_dictionary WHERE occurence = 1 ), pivot_look_forward AS ( SELECT dict.*, p.context, string_to_as_string_from_id = MAX(CASE WHEN context = 'f' THEN p.id END) OVER (PARTITION BY string) FROM dictionary dict CROSS APPLY ( VALUES (id, string_from, 'f'), (id, string_to, 't') ) p(id, string, context) ), mapping_sequence AS ( SELECT map.* FROM pivot_look_forward dict CROSS APPLY ( SELECT CASE WHEN dict.string_to_as_string_from_id > dict.id THEN 1 END ) future_key(id) CROSS APPLY ( SELECT 2, dict.id, dict.string_from, dict.string_to WHERE future_key.id IS NULL AND dict.string_from NOT IN (N'<', N'>') UNION ALL SELECT 2, dict.id, dict.string_from, N'<' + dict.string_from + N'>' WHERE future_key.id IS NOT NULL AND dict.string_from NOT IN (N'<', N'>') UNION ALL SELECT 3, dict.id, N'<' + dict.string_from + N'>', dict.string_to WHERE future_key.id IS NOT NULL AND dict.string_from NOT IN (N'<', N'>') UNION ALL SELECT 0, dict.id, dict.string_from, N'<' + dict.string_from + N'>' WHERE dict.string_from IN (N'<', N'>') UNION ALL SELECT 4, dict.id, N'<' + dict.string_from + N'>', dict.string_to WHERE dict.string_from IN (N'<', N'>') ) map(phase_id, id, string_from, string_to) WHERE dict.context = 't' UNION ALL SELECT 1, 0, N'<<<>>', N'<<>' WHERE @from_chars LIKE N'%<%' AND @from_chars LIKE N'%>%' ) SELECT @string = REPLACE(@string, string_from, string_to) FROM mapping_sequence ORDER BY phase_id, id ; RETURN @string; END
Msg 137 Level 15 State 2 Line 83 Must declare the scalar variable "@from_chars".
 hidden batch(es)


WITH tests(id, replace_string, from_chars, to_chars) AS ( SELECT 1, N'abc<defghijklm<nop qr stuvw<>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'a<>b cz', N'b><c-da' UNION ALL SELECT 4, N'abc\defghijk lmnop qrstuvw\x\xyz\\<>', N'abc ', N'bcd-' UNION ALL SELECT 5, N'abc', N'aba' , N'bab' ) SELECT *, dbo.Translate(replace_string, from_chars, to_chars),TRANSLATE(replace_string, from_chars, to_chars) FROM tests EXCEPT SELECT *, TRANSLATE(replace_string, from_chars, to_chars), TRANSLATE(replace_string, from_chars, to_chars) FROM tests ;
Msg 4121 Level 16 State 1 Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Translate", or the name is ambiguous.
 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 ;
Msg 4121 Level 16 State 1 Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.translate", or the name is ambiguous.
 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 ;
Msg 4121 Level 16 State 1 Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.translate", or the name is ambiguous.
 hidden batch(es)