This project has moved and is read-only. For the latest updates, please go here.
1
Vote

Problem with particular string

description

Hi,
when I user Group_Concat function, for some data a receive this message:

A .NET Framework error occurred during execution of user-defined routine or aggregate "GROUP_CONCAT":
System.Text.EncoderFallbackException: Unable to translate Unicode character \uD83D at index 65 to specified code page.
System.Text.EncoderFallbackException:
at System.Text.EncoderExceptionFallbackBuffer.Fallback(Char charUnknown, Int32 index)
at System.Text.EncoderFallbackBuffer.InternalFallback(Char ch, Char& chars)
at System.Text.UTF8Encoding.GetByteCount(Char
chars, Int32 count, EncoderNLS baseEncoder)
at System.Text.UTF8Encoding.GetByteCount(String chars)
at System.IO.BinaryWriter.Write(String value)
at GroupConcat.GROUP_CONCAT.Write(BinaryWriter w)

I thin is some particular character that cause the problem. Could you help me?
Thanks and kind regards,
Elia.

comments

opcthree wrote Jan 9 at 1:31 PM

Can you please post code that reproduces the problem?

eliao wrote Jan 9 at 4:35 PM

I found the problem, in the string it was an emoticon (smile). This why the sql server table is populate with text from a mobile app. Removing smile emoticon, it work.

opcthree wrote Jan 10 at 5:07 AM

Hi eliao, Please check the data in your database to ensure they are all valid UCS-2 characters or surrogate pairs. After some research it appears that there is an emoji character that is rationalized as a UCS-2 surrogate pair however if only one half of the pair is stored to the database it falls into the range of a non-mappable character and you see the error you are seeing. Here is a repro that shows a happy emoji working:
WITH    cte(id, details)
          AS (
              SELECT    1,
                        'Large double double' + NCHAR(0xD83D) + NCHAR(0xDE0A)
              UNION ALL
              SELECT    1,
                        'Medium double double'
             )
    SELECT  cte.id,
            dbo.GROUP_CONCAT(cte.details)
    FROM    cte
    GROUP BY cte.id;
Now take out the second character and you see the error you reported:
WITH    cte(id, details)
          AS (
              SELECT    1,
                        'Large double double' + NCHAR(0xD83D)
              UNION ALL
              SELECT    1,
                        'Medium double double'
             )
    SELECT  cte.id,
            dbo.GROUP_CONCAT(cte.details)
    FROM    cte
    GROUP BY cte.id;
Run this query against your database to extract the value of the string causing the issue as binary and provide the results back to me here so I can research further and confirm what is happening in your system:

SELECT column_name, CAST(column_name AS VARBINARY(MAX))
FROM table;