i made a solution and posting so others dont spend the day doing the same
select ab_test.dbo.GetEmojisInString('👌💖🤷â€â™‚ï¸ðŸ˜ŽðŸ±â€ðŸ’»ðŸ˜‰â¤ðŸ±â€ðŸ‘¤ðŸ¤žðŸ¤£ðŸ‘💕✌ðŸ±â€ðŸðŸ’‹ðŸŽ‚🎉🤦â€â™‚ï¸ðŸ˜ŠðŸŒ¹ðŸ‘ðŸ±â€ðŸ‰ðŸŽ¶ðŸ˜ðŸ¤¦â€â™€ï¸ðŸ˜ðŸ™ŒðŸ±â€ðŸš€ðŸ˜œðŸ˜˜ðŸ±â€ðŸ‘“😢😒🤳😂')
will return
♂️❤✌♂️♀️
there are 5 functions below, probrably not perfect and maybe shorter / better ways but this functions. if any bugs let me know.
NOTE : i had to split over two databases as for this to work the collation needs to have _CS, and the bi_library database below in my solution i could not change this as database was locked so for now just created a ab_test db.
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
