Omvandla text i UTF-8 till standard ANSI


CREATE OR ALTER FUNCTION dbo.ConvertUtf8Ansi
(
        @source VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN 
        RETURN  CASE
                        -- 2 byte
                        WHEN @source LIKE '%[ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞß][€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿]%' THEN CAST(CONCAT('<?xml version="1.0" encoding="UTF-8"?><![CDATA[', @source, ']]>') AS XML).value('(text()[1])', 'VARCHAR(MAX)')
                        -- 3 byte
                        WHEN @source LIKE '%[àáâãäåæçèéêëìíîï][€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿][€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿]%' THEN CAST(CONCAT('<?xml version="1.0" encoding="UTF-8"?><![CDATA[', @source, ']]>') AS XML).value('(text()[1])', 'VARCHAR(MAX)')
                        -- 4 byte
                        WHEN @source LIKE '%[ðñòóôõö÷][€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿][€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿][€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿]%' THEN CAST(CONCAT('<?xml version="1.0" encoding="UTF-8"?><![CDATA[', @source, ']]>') AS XML).value('(text()[1])', 'VARCHAR(MAX)')
                        -- 1 byte
                        ELSE @source
                END;
END;

Have any Question or Comment?

Leave a Reply