Why are fields with Text converted to NULL?

Posted on 2011-04-26
Medium Priority
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

by:Ephraim Wangoya
ID: 35467143

ASCII 8482, am I missing something?
LVL 21

Expert Comment

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

Author Comment

ID: 35467301
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.
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

LVL 17

Expert Comment

by:Chris Mangus
ID: 35467431
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35467491
>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

by:Chris Mangus
ID: 35467592
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

lludden earned 2000 total points
ID: 35468074
use NCHAR() instead of char for unicode characters


Author Closing Comment

ID: 35468272
thanks. I think that is what I was looking for.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

850 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