Hebrew Encoding in SQL Server2005

Hebrew encoding in sql server2005

Use the N prefix so the string literal is interpreted as unicode not char under the code page of whatever your database's default collation is

declare @userName nvarchar(50)
set @userName=N'איש2'    
print @userName

Displaying Hebrew text in ASP

Make sure the column is set to store Unicode. For example, nvarchar instead of varchar, or ntext instead of text.

ISNULL expression shows ??? -s in the result

Use N before string definition

SELECT 
    Field.Table1, 
    Field.Table2, 
    ISNULL(Field.Table3, N'My text here') 
FROM Table.

How to pass SQL stored procedure NVARCHAR parameter with Hebrew?

The problem is simply that the string literal used in the LIKE condition is not prefixed with an N to indicate that it is a Unicode string. The following example shows the difference:

DECLARE  @search_criteria NVARCHAR(100) = N'ב';

IF @search_criteria LIKE '%[אבגדהוזחטיחכךילמנפףערקשת]%'
BEGIN
    PRINT 'WithOUT "N"-prefix';
END;

IF @search_criteria LIKE N'%[אבגדהוזחטיחכךילמנפףערקשת]%'
BEGIN
    PRINT 'WITH "N"-prefix';
END;

Returns:

WITH "N"-prefix

To more easily understand why there is this difference in behavior, consider the following:

-- when the DB has a default collation of Latin1_General_100_CI_AS_SC (code page 1252)
SELECT  '%[אבגדהוזחטיחכךילמנפףערקשת]%'
-- %[????????????????????????]%

-- when the DB has a default collation of Hebrew_100_CI_AS_SC (code page 1255)
SELECT  '%[אבגדהוזחטיחכךילמנפףערקשת]%'
-- %[אבגדהוזחטיחכךילמנפףערקשת]%

The string literals are parsed in the code page used by the default collation of the current database. If the code page can support these characters, then not prefixing with the upper-case "N" will work. But, if those characters do not exist in that code page, then they are converted into "?"s.

write unicode data to mssql with python?

You need to change the datatype of your column. text is deprecated, and varchar(MAX) should be used instead, however, neither can store unicode characters. To store unicode characters you would need to use ntext, which is also deprecated; you need to use nvarchar(MAX).

To change your column's definition, you can use this pseudo-SQL (You'll need to replace the parts in braces ({}) with the appropriate object names):

ALTER TABLE {YourTable} ALTER COLUMN {YourColumn} nvarchar(MAX);

Edit: note, this will not restore any data lost in your column. Once a non-ANSI character is inserted into a varchar (or similar) datatype the data is immediately lost and cannot be recovered apart from by changing the datatype and reentry.

sqlalchemy UnicodeDecodeError: 'utf8' codec can't decode byte 0xe7 when trying to select all from msssql 2005 table

found the answer - a classic wtf. seems the the production server had a slightly different configuration then the test server (although both were supposed to be the same) - so instead of nvarchar my application model was expecting it got varchar. thanks for the help. writing the question helped me clear my thoughts anyway

How to solve unable to switch the encoding error when inserting XML into SQL Server

Although a .net string is always UTF-16 you need to serialize the object using UTF-16 encoding.
That sould be something like this:

public static string ToString(object source, Type type, Encoding encoding)
{
    // The string to hold the object content
    String content;

    // Create a memoryStream into which the data can be written and readed
    using (var stream = new MemoryStream())
    {
        // Create the xml serializer, the serializer needs to know the type
        // of the object that will be serialized
        var xmlSerializer = new XmlSerializer(type);

        // Create a XmlTextWriter to write the xml object source, we are going
        // to define the encoding in the constructor
        using (var writer = new XmlTextWriter(stream, encoding))
        {
            // Save the state of the object into the stream
            xmlSerializer.Serialize(writer, source);

            // Flush the stream
            writer.Flush();

            // Read the stream into a string
            using (var reader = new StreamReader(stream, encoding))
            {
                // Set the stream position to the begin
                stream.Position = 0;

                // Read the stream into a string
                content = reader.ReadToEnd();
            }
        }
    }

    // Return the xml string with the object content
    return content;
}

By setting the encoding to Encoding.Unicode not only the string will be UTF-16 but you should also get the xml string as UTF-16.

<?xml version="1.0" encoding="utf-16"?>

---

Related Topics