將文本表情符號從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 e?¤? xxx。MySQL表是utf8mb4，我已經為鏈接服務器設置了ODBC配置以使用它。我正在使用MySQL ODBC 5.3.13
任何建議都將不勝感激。SQL Server版本是2016，我已經看到了要放的例子
select N'??'
等，但不知道如何將其應用于上述查詢。
Fin?？?似乎是4個字符
e = 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('e?‘?e?’–e?¤·a€a????e???e?±a€e?’?e??‰a¤e?±a€e?‘¤e?¤?e?¤￡e?‘e?’?a??e?±a€e?e?’?e???e??‰e?¤|a€a????e???e??1e?‘e?±a€e?‰e???e??e?¤|a€a?€??e??e???e?±a€e??€e???e???e?±a€e?‘"e??￠e??’e?¤3e???')
將返回
???????♂???????????????????????????????????????♂???????????????????♀???????????????????????????
下面有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 --'a??e??e?’?e?¤·a€a????e?¤3e?±a€e?‘"e?±a€e??€e?±a€e?‰e???e?’?e?¤?e??‰e?‘?e?¤|a€a?€??e?±a€e?e?’–e??’e???e??e?‘e?¤|a€a????e?‘e?±a€e?‘¤e?±a€e?’?e???e???e???e???e??￠e???e???e??1e??‰e?¤￡a¤e?¤·a€a?€??'
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屋！
查看全文
