将文本表情符号从MySQL复制到SQL Server [英] Copying emojis in text from MySQL to 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
