How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns?

Question

I've been struggling for days with a problem on a MS SQL database. Here is the situation: I have an old Open Source PHP application (Limesurvey version 1.92) running on a Windows 2008R2 server, with SQL2008R2 as RDBMS. I need to migrate the surveys in the application to a new instance, running on Linux with MySQL. The new instance is version 3, and database structure between both versions is different. The scenario I tried to use is upgrading the old version on the old server, and then exporting the surveys as export files, which I can import into the new application. It works well. Except that I'm stuck because of a problem of character encoding. The old application (web application) uses UTF-8. But the database uses only VARCHAR and TEXT columns. Therefore UTF-8 characters are stored using several bytes in these columns : é is stored as é for example. When I upgrade the application, the upgrade script changes the structure of the tables: TEXT columns become NVARCHAR(MAX), but the content in the column is not converted to UTF-8. So I'm searching for a way to change the content in the NVARCHAR(MAX) columns from ISO8859 to UTF-8. The database has approx 200 tables and is 600MB.

Answer 1

SQL Server 2008r2 dopes not support UTF8 (such support does not arrive until the upcoming 2019 release) it only supports UCS2 (essentially a subset of UTF16). Because of this there is unlikely to be a practical solution in SQL Server itself.

You may have to code up a tool to pull the data out, convert it, and update the DB with the newly converted values.

On rereading the question, I notice you mention mysql as the target database, though your tags do not mention that. The types you mention (NVARCHAR etc.) are not mysql types. You may need to clarify the question and update the tags.

Answer 2

The question is a little unclear due to indicating the need to migrate to MySQL yet only mentioning SQL Server datatypes. Are you meaning to say that the "upgrade" process is converting everything within SQL Server first, and then you will export the data and import it into MySQL? Depending on the specifics of the situation, it sounds like you have at least a couple of options:

1. If the app really did store the UTF-8 bytes such that you have é instead of é, you can probably export the data from the VARCHAR / TEXT columns into a text file using the code page of the collation of the columns. You can find this using the following query:

   SELECT col.[name],
          col.[collation_name],
          COLLATIONPROPERTY(col.[collation_name], 'CodePage') AS [CodePage]
   FROM   sys.columns col
   WHERE  col.[object_id] = OBJECT_ID(N'dbo.InfoSchemaBug')
   AND    col.[collation_name] IS NOT NULL;
   

   Using the same code page will ensure that the bytes written to the file are what is already in the columns, and the bytes (not the characters) in the columns are correct.

   Once the data is exported, simply import into MySQL, but this time tell the import process that the file is actually encoded as UTF-8. Since the bytes were UTF-8 to begin with, it is still UTF-8 data (as long as the bytes didn't change during export).

2. Since internal UTF-8 support doesn't start until SQL Server 2019, you can still convert the data in-place into Unicode (UTF-16) such that it can be exported to a "Unicode" (i.e. UTF-16 Little Endian) file that should still be importable into MySQL as long as you indicate that the file is encoded as UTF-16 LE. You can try the following which uses a conversion to XML (from VARCHAR, but is written to handle if you run against the already converted to NVARCHAR column) to handle the conversion:

   CREATE TABLE #UTF8bytes
   (
     [Data] NVARCHAR(MAX)
   );
   
   INSERT INTO #UTF8bytes ([Data]) VALUES (N'é&');
   
   SELECT *,
          CONVERT(NVARCHAR(MAX),
                  CONVERT(XML,
                      '<?xml version="1.0" encoding="utf-8" standalone="yes" ?><!--'
                      + CONVERT(VARCHAR(MAX), tmp.[Data] COLLATE Latin1_General_100_CI_AS)
                      + '-->'
                         )
                 ) AS [Converted]
   FROM   #UTF8bytes tmp;
   /*
   Data    Converted
   é&     <!--é&-->
   */
   
   
   UPDATE tmp
   SET    tmp.[Data] = CONVERT(NVARCHAR(MAX),
                  CONVERT(XML,
                      '<?xml version="1.0" encoding="utf-8" standalone="yes" ?><!--'
                      + CONVERT(VARCHAR(MAX), tmp.[Data] COLLATE Latin1_General_100_CI_AS)
                      + '-->'
                         )
                 )
   FROM   #UTF8bytes tmp;
   
   
   SELECT *
   FROM   #UTF8bytes tmp;
   -- <!--é&-->
   

   After that you just need to remove the <!-- from the beginning of each value, and the --> at the end of each value. Those were included to force the existing data to be XML comments, which accepts XML-special characters such as <, ", and &. This should work as long as the data does not already include either the <!-- or the --> in it. And, you could likely add a SUBSTRING or two to the UPDATE statement such that those two values are removed in the same operation as the conversion.