locked
utf-8 in database varchar RRS feed

  • Question

  • User-805177867 posted

    We have existing SQL Server databases with all text fields defined as varchar (not nvarchar) and the text in them is actually UTF-8. Our old system (written in Delphi) will read and write those strings correctly and display the international characters on a web page etc. We are rewriting our system in ASP.NET and using Datasets to access the data. Reading/writing the data does not treat the data as utf-8. How can I get it to treat it as utf-8?

     

    Friday, May 18, 2007 12:29 AM

Answers

  • User-319574463 posted

    Why not upgrade the VARCHAR to NVARCHAR? If you used stored procedures for your existing Delphi programe, leave the existimg sp with VARCHAR datatype and create a new set with NVARCHAR arguments. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 19, 2007 6:22 PM

All replies

  • User-319574463 posted

    Why not upgrade the VARCHAR to NVARCHAR? If you used stored procedures for your existing Delphi programe, leave the existimg sp with VARCHAR datatype and create a new set with NVARCHAR arguments. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 19, 2007 6:22 PM
  • User-805177867 posted

    So can the stored procedure return a field as a different datatype to the one stored? How is this conversion handled? Can you ensure that the varchar is encoded as utf-8 and not using a codepage?

    Monday, May 21, 2007 7:31 PM
  • User-319574463 posted

    >So can the stored procedure return a field as a different datatype to the one stored?
    Yes!

    >How is this conversion handled?
    Explicitly using using CONVERT (although CAST can also be used)

    >Can you ensure that the varchar is encoded as utf-8 and not using a codepage?
    You can also specify an explicit collating sequence using COLLATE

    The existing table will need to be changed from VARCHAR to NVARCHAR

    Tuesday, May 22, 2007 2:47 AM
  • User-805177867 posted

    I don't use stored procedures in the existing application. And there are two many tables to create stored procedures for them all. The code would have to change too much. So for now I am stuck with varchar fields. Is it not possible to cast the nvarchar to a varchar when saving the data to the database? I tried to write a stored function and it gives and exception. My function was  :-

    CREATE FUNCTION dbo.fnStringWrite
    (
      @S nvarchar(100)
    )
    RETURNS varchar(100)
    AS
    BEGIN
      DECLARE @RETURN varchar(100)
      SET @RETURN = cast(@S as varchar(100))
      RETURN @RETURN
    END

    Wednesday, May 23, 2007 12:21 AM
  • User-805177867 posted

    Is it possible for a function to return a varchar with different lengths?

    It seems as though I need to decalre the return variable as say type varchar(100). I need to be able to handle various string lengths. Can I pass in the length of the string and use that when defining the varchar inoput and output lengths? Otherwise I need a function for every possible string field length.

    Wednesday, May 23, 2007 12:26 AM
  • User-805177867 posted

    How do you use COLLATE? I thought the collate only affected sort order or comparisons?

    Does it also affect how the data is stored? If it does I assume it only affects which codepage is used to convert characters. This is not the same as UTF-8 encoding. I don't think there is a collation to force storage in utf-8 format.

    Wednesday, May 23, 2007 12:31 AM
  • User-319574463 posted

    COLLATE can be used in a WHERE clause to compare data in different collationg sequences as in

    WHERE TABLE1.A = TABLEB.A COLLATE Latin1_General_CI_AS

    Make a copy of the database and convert all the database VARCHAR to NVARCHAR. The casting should be in the old set of stored procedures. The simplest way forward however would be to update the delphi program to use NVARCHAR. That way the conversion issue will no longer arise.

    Wednesday, May 23, 2007 2:37 AM