Why are fields with Text converted to NULL?

Posted on 2011-04-26
Last Modified: 2012-06-27
Today's odd question.

I am trying to replace ASCII characters with their HTML equivilents. The following update works fine.
Update tableA set FieldName = Replace(FieldName,Char(153),'™')

But the trademark can also be represented by ASCII code 8482. I rearrange the query for this:
Update tableA set FieldName = Replace(FieldName,Char(8482),'™')

The result of the second UPDATE is that all the data in FieldName is lost and converted a NULL. This happens for every record in the table. This really is annoying.

What am I doing incorrectly?
Question by:mpdillon
    LVL 32

    Expert Comment


    ASCII 8482, am I missing something?
    LVL 21

    Expert Comment

    by:Alpesh Patel
    May be it is part of HTML encoding.

    Author Comment

    See the bottom of this link.

    I just noticed that ASCII has been dropped from the header of this last table. Although it is in the same column as the table(s) above.
    LVL 17

    Expert Comment

    From Books Online:

    Argument for CHAR() is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.

    You're using a value greater than 255.  There are no ASCII numbers greater than 255.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >The result of the second UPDATE is that all the data in FieldName is lost and converted a NULL. This happens for every record in the table
    this cannot be. at least not for the UPDATE in itself.

    so, either, there is a trigger on the table that modifies the behavior,
    or you don't "look" at the table output correctly

    replace() function will NOT return NULL, unless there is a NULL input.
    LVL 17

    Expert Comment

    There IS a NULL input, CHAR generates it with an argument value greater than 255.

    REPLACE also returns NULL if any argument is NULL.

    In essence, the OP is directly updating the field to NULL.
    LVL 18

    Accepted Solution

    use NCHAR() instead of char for unicode characters


    Author Closing Comment

    thanks. I think that is what I was looking for.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now