We help IT Professionals succeed at work.

Why are fields with Text converted to NULL?

Medium Priority
209 Views
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?
thanks,
pat
Comment
Watch Question

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

ASCII 8482, am I missing something?
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
May be it is part of HTML encoding.

Author

Commented:
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.
Chris MangusDatabase Administrator
CERTIFIED EXPERT

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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.
Chris MangusDatabase Administrator
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks. I think that is what I was looking for.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.