Asp.net convert and display varchar Chinese characters on the fly

user_1306836 0
Viewed 75

I made a mistake years ago and created varchar columns in SQL Server to keep urls. And so my website can not display Chinese URLs properly. Now the database is huge and there are too many relations, indexes, constraints etc... And so it'd be extremely hard to change varchar columns to nvarchar.

I was planning to add new nvarchar columns and change all the stored procedures and asp.net classes&codes and then delete the existing varchar columns. But I just realized that google somehow can convert varchar data to nvarchar.

For example following is a website url in my database which is a Chinese(or japanese, not sure actually) website and when I search in google, google is able to find and display as Chinese characters.

So I wonder if there is a way to convert varchar characters on the fly with C# or T-SQL. Google is probably saving both versions and this is not possible as far as I know but I just wanted to ask if there is a way to sort this out.

enter image description here

asp.net sql
2 Answers
using System;
using System.Globalization;
                    
public class Program
{
    public static void Main()
    {
        var idn = new IdnMapping();
        var url = "xn--ssd-7b7fj34n.com";
        var punyCode = idn.GetAscii(url);
        var url2 = idn.GetUnicode(punyCode);
        Console.WriteLine(url2); // result is ssd換装.com
    }
}

Ideone demo

user_27944090

I put a script that can easily change the type of data columns. The following code changes the entire varchar column to nvarchar with the same data length.

Note: Enter the name of the database you want instead of :

Use dataBaseName
Go

Full script

use dataBaseName
go

--Container to Insert Id which are to be iterated
Declare @temp Table
(
  tablename varchar(100),
  columnname varchar(100),
  columnlength varchar(100),
  columntype varchar(100)
)
--Container to Insert records in the inner select for final output

Insert into @temp

SELECT t.TABLE_NAME,c.COLUMN_NAME,c.CHARACTER_MAXIMUM_LENGTH,c.DATA_TYPE 
FROM information_schema.tables t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE DATA_TYPE = 'varchar'

-- Keep track of @temp record processing
Declare @tablename varchar(100)
Declare @columnname varchar(100)
Declare @columnlength varchar(100)
Declare @columntype varchar(100)
Declare @SQL VarChar(1000)

While((Select Count(*) From @temp)>0)
Begin
   Set @tablename=(Select Top 1 tablename From @temp)
   Set @columnname=(Select Top 1 columnname From @temp)
   Set @columnlength=(Select Top 1 columnlength From @temp)
   Set @columntype='nvarchar'


--set null value with empty value

   SELECT @SQL = 'UPDATE ' + @tablename + ' SET ' + @columnname + ' = 0 WHERE ' + @columnname  + ' IS NULL'
   Exec ( @SQL)


   SELECT @SQL = 'ALTER TABLE '
   SELECT @SQL = @SQL + @tablename
   SELECT @SQL = @SQL + ' ALTER COLUMN ' + @columnname + ' ' + @columntype + '(' + @columnlength + ') NOT NULL'

--execute command
   Exec (@SQL)
   Delete @temp Where tablename=@tablename and columnname = @columnname
End

Before running, create a test database and put a backup of the original database in it and run the script on the test database. If the result is successful, make a backup of the test database and replace it with the original database.

user_150195360
Related