mpdillon
asked on
Why are fields with Text converted to NULL?
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(848
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?
thanks,
pat
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(848
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?
thanks,
pat
May be it is part of HTML encoding.
ASKER
See the bottom of this link.
http://www.ascii.cl/htmlcodes.htm
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.
http://www.ascii.cl/htmlcodes.htm
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.
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.
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.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>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.
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.
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.
REPLACE also returns NULL if any argument is NULL.
In essence, the OP is directly updating the field to NULL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks. I think that is what I was looking for.
ASCII 8482, am I missing something?