将文本表情符号从MySQL复制到SQL Server

本文介绍了将文本表情符号从MySQL复制到SQL Server的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用链接服务器将数据从MySQL复制到SQL Server。

SELECT comment FROM openquery(my_linked_server, 'SELECT comment FROM search_data');

MySQL表列中的文本为xxx 🤘 xxx。当我在SQL Server中收到它时，它是xxx 🤘 xxx。MySQL表是utf8mb4，我已经为链接服务器设置了ODBC配置以使用它。我正在使用MySQL ODBC 5.3.13

任何建议都将不胜感激。SQL Server版本是2016，我已经看到了要放的例子

select N'🤘'

等，但不知道如何将其应用于上述查询。

FinŸ？˜似乎是4个字符

ð = u00f0 , dec = 240
Ÿ = u0178 , dec = 276
¤ = u00a4 , dec = 164
˜ = u02dc , dec =  732

🤘=ud83e，dec=55358

有趣的是，这根本不起作用

select nchar(unicode(N'🤘')),unicode(N'🤘') 

返回�符号

推荐答案

我做了一个解决方案并发布，这样其他人就不会花一整天的时间做同样的事情

select ab_test.dbo.GetEmojisInString('👌💖🤷â€â™‚ï¸ðŸ˜ŽðŸ±â€ðŸ’»ðŸ˜‰â¤ðŸ±â€ðŸ‘¤ðŸ¤žðŸ¤£ðŸ‘💕✌ðŸ±â€ðŸðŸ’‹ðŸŽ‚🎉🤦â€â™‚ï¸ðŸ˜ŠðŸŒ¹ðŸ‘ðŸ±â€ðŸ‰ðŸŽ¶ðŸ˜ðŸ¤¦â€â™€ï¸ðŸ˜ðŸ™ŒðŸ±â€ðŸš€ðŸ˜œðŸ˜˜ðŸ±â€ðŸ‘"😢😒🤳😂')

将返回

👌💖🤷‍♂️😎🐱‍💻😉❤🐱‍👤🤞🤣👏💕✌🐱‍🏍💋🎂🎉🤦‍♂️😊🌹👍🐱‍🐉🎶😍🤦‍♀️😁🙌🐱‍🚀😜😘🐱‍👓😢😒🤳😂

下面有5个函数，可能不是完美的，也可能是更短/更好的方法，但这是有效的。如果有任何错误，请让我知道。

注意：我必须拆分两个数据库，因为排序规则需要有_CS，而在我的解决方案中，下面的bi_库数据库我无法更改，因为数据库已锁定，因此目前只创建了一个ab_test数据库。

USE [bi_library]
GO

CREATE FUNCTION [dbo].[GetDecimalFromOtherBase]
(  @p_in_value   varchar(100),
   @p_from_base  int -- ie 16 for hex, 8 for octal, 2 for bin
) returns int
as
begin
    declare @l_in_value varchar(100) = reverse(@p_in_value) -- spin backwards as maths works in easier this way
    declare @l_from_base varchar(100) = @p_from_base--@p_from_base --= @p_in_value
    declare @l_pos int = 1
    declare @l_char char(1)
    declare @l_val int = 0
    declare @l_total int = 0

    while @l_pos<= len(@l_in_value)
    begin
       set @l_char = substring(@l_in_value,@l_pos,1)

       if isnumeric(@l_char)=0
       begin
          set @l_val = ascii(@l_char)-55 -- convert A to 10, F to 15 etc
       end 
       else
       begin
          set @l_val = @l_char
       end

       set @l_total = @l_total + (power(@l_from_base,@l_pos-1)*@l_val)
       set @l_pos=@l_pos+1
    end
    return @l_total
end
GO

CREATE FUNCTION [dbo].[GetOtherBaseFromDecimal]
(  @p_in_value   int,
   @p_to_base  int -- ie 16 for hex, 8 for octal, 2 for bin
) returns varchar(100)
as
begin
    -- convert decimal to other base
    declare @l_dec int = @p_in_value
    declare @l_ret_str varchar(100) = ''
    declare @l_rem int = 0
    declare @l_rem_char char(1) = '?'
    while @l_dec > 0
    begin
          set @l_rem = @l_dec % @p_to_base
          if @l_rem >= 10
          begin
             set @l_rem_char = char(55+@l_rem)
          end
          else
          begin
             set @l_rem_char = cast(@l_rem as varchar)
          end

          set @l_ret_str = @l_ret_str + @l_rem_char
          set @l_dec = @l_dec / @p_to_base
    end
    return reverse(@l_ret_str)
end
GO


CREATE FUNCTION [dbo].[GetBaseFromOtherBase]
(  @p_in_value varchar(100),
   @p_in_base  bigint, -- ie 16 for hex, 8 for octal, 2 for bin
   @p_to_base  bigint -- ie 16 for hex, 8 for octal, 2 for bin
) returns varchar(100)
as
begin
   return bi_library.dbo.GetOtherBaseFromDecimal(bi_library.dbo.GetDecimalFromOtherBase(@p_in_value,@p_in_base),@p_to_base)
end
GO

USE [ab_test]
GO



ALTER function [dbo].[GetEmojisInString] (@p_in_string nvarchar(max)) returns nvarchar(max)
as
begin
    declare @l_string varchar(1000) = @p_in_string --'✌ðŸ˜ðŸ’‹ðŸ¤·â€â™‚ï¸ðŸ¤³ðŸ±â€ðŸ‘"ðŸ±â€ðŸš€ðŸ±â€ðŸ‰ðŸ˜ŠðŸ’•ðŸ¤žðŸ˜‰ðŸ‘ŒðŸ¤¦â€â™€ï¸ðŸ±â€ðŸðŸ’–😒😘ðŸ˜ðŸ‘🤦â€â™‚ï¸ðŸ‘ðŸ±â€ðŸ‘¤ðŸ±â€ðŸ’»ðŸ™ŒðŸŽ‚😎😂😢😜🎶🌹🎉🤣â¤ðŸ¤·â€â™€ï¸'
    declare @l_pos    int = 1
    declare @l_char   varchar(1)
    declare @l_cont_extended_ascii int = 0
    declare @l_byte1_hex varchar(2)
    declare @l_byte2_hex varchar(2)
    declare @l_byte3_hex varchar(2)
    declare @l_byte4_hex varchar(2)
    declare @l_hex_char  varchar(2)
    declare @l_str       nvarchar(max) = ''
    declare @l_dec_value_found int

    while   @l_pos  <= len(@l_string)
    begin
       set @l_char = substring(@l_string,@l_pos,1)
       --print(ascii(@l_char))
       if ascii(@l_char)>=128 
       begin
          set @l_cont_extended_ascii = @l_cont_extended_ascii+1
          --print(@l_char)
          set @l_hex_char = bi_library.dbo.GetOtherBaseFromDecimal(ascii(@l_char),16)
          if  @l_cont_extended_ascii = 1 
          begin 
             set @l_byte1_hex = @l_hex_char 
             --print('set byte 1')
          end
          else if @l_cont_extended_ascii = 2 
          begin 
             --print('set byte 2')
             set @l_byte2_hex = @l_hex_char
             set @l_dec_value_found = bi_library.dbo.GetDecimalFromOtherBase(
                       reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte1_hex,16,2)),1,6))+
                       reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte2_hex,16,2)),1,6))
                  ,2)
              if @l_dec_value_found between 128/*U+0080*/ and 2047/*U+07FF  */
              begin
                 --print('2 byte emoji found')
                 set @l_str = @l_str+coalesce(nchar(@l_dec_value_found),'?')
                 set @l_cont_extended_ascii = 0
              end

          end
          else if @l_cont_extended_ascii = 3 
          begin 
              --print('set byte 3')
              set @l_byte3_hex = @l_hex_char 
              set @l_dec_value_found = bi_library.dbo.GetDecimalFromOtherBase(
                       reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte1_hex,16,2)),1,4))+
                       reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte2_hex,16,2)),1,6))+
                       reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte3_hex,16,2)),1,6))
                  ,2)
              if @l_dec_value_found between 2048/*U+0800*/ and 65535/*U+FFFF*/
              begin
                 --print('3 byte emoji found')
                 set @l_str = @l_str+coalesce(nchar(@l_dec_value_found),'?')
                 set @l_cont_extended_ascii = 0
              end
               --print(@l_str)
          end
          else if @l_cont_extended_ascii = 4 begin set @l_byte4_hex = @l_hex_char

                   set @l_dec_value_found = bi_library.dbo.GetDecimalFromOtherBase(
   reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte1_hex,16,2)),1,3))+
   reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte2_hex,16,2)),1,6))+
   reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte3_hex,16,2)),1,6))+
   reverse(substring(reverse(bi_library.dbo.GetBaseFromOtherBase(@l_byte4_hex,16,2)),1,6))
                      ,2)
                   if @l_dec_value_found between 65536/*U+10000*/ and 1114111/*U+10FFFF*/
                   begin
                      --print('4 byte emoji found')
                      set @l_str = @l_str+coalesce(nchar(@l_dec_value_found),'?')
                      set @l_cont_extended_ascii = 0
                   end
                   else
                   begin
                      --print('out of range byte emoji found')
                      set @l_str = @l_str+@l_char
                   end
                    --print(@l_str)
             --end


             set @l_cont_extended_ascii = 0  
          end
       end
       else
       begin
          --print('snapping')
          set @l_str = @l_str+@l_char
          set @l_cont_extended_ascii = 0
           --print(@l_str)
       end


       set @l_pos = @l_pos+1
    end
    --print(@l_str)
    return @l_str
end

CREATE function [dbo].[HasEmojisInString] (@p_in_string nvarchar(max)) returns int
as
begin
    declare @l_string_emojified varchar(1000)
    set @l_string_emojified = dbo.GetEmojisInString(@p_in_string) 
    if @l_string_emojified <> @p_in_string
    begin
       return 1
    end
    return 0
end
GO

这篇关于将文本表情符号从MySQL复制到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！