Re: I'm stuck replace(column, '''') and replace(column,chr(39)) not working

This is what happens when you work for more the 14 hours straight. troubleshooting skills start to degrade big time. I should have walked away from the problem earlier but wanted to push on.

1. there was a nonprintable character in position 1. This was confirmed by select ascii(substr(ein,1,1)) from customers where substr(ein,1,1) != 0;
2. I was testing in the same session.
3. I was using sql developer
4. I knew every column has a leading 0, therefore update customers set ein = substr(ein,2) where substr(ein,1,1) != 0;

update customer set ein = replace(ein,chr(39)); That fixed it.

Thanks,

-Rob

On 9/2/2016 2:10 AM, Kim Berg Hansen wrote:
> Hi, Rob
>
> Grasping at straws, but if your update says it updates but data hasn't
> changed, I'd ask if you are sure that it is actually a normal single
> quote in your data? Could it be a "smart quote" for example
> originating from a Windows Office auto-replace rule? You could test it
> with:
>
> select ascii(substr(ein,1,1)) from customers;
>
> Then see if it is actually 39?
> (Or it could be maybe a UTF multibyte character?)
>
> Just a wild guess...
>
>
> Regards
>
>
> Kim Berg Hansen
>
> http://www.kibeha.dk
> kibeha_at_kibeha.dk <mailto:kibeha_at_kibeha.dk>
> _at_kibeha <http://twitter.com/kibeha>
>
>
>
> On Fri, Sep 2, 2016 at 2:40 AM, Rob Lockard <rob_at_oraclewizard.com
> <mailto:rob_at_oraclewizard.com>> wrote:
>
> This should be easy, maybe i've been working it for too long, or
> don't know.
>
> Anyway, I have a column that wound up getting populated with a
> leading ' (single quote)
>
> update customers set ein = replace(ein,''''); and update customers
> set ein = replace(ein,chr(39)); does not work. Well, the message
> says updated but when I query the table, it's still there. There
> is something simple that I'm missing and for the life of me, I
> can't figure it out.
>
> The definition of insanity is doing the same thing over and over
> expecting different results. By that definition; I've been insane
> for the past two hours.
> -Rob
>
> --
> ================================
> "Rules are for the obedience of fools and the guidance of wise
> men" -Douglas Bader
> Robert P. Lockard, Oracle ACE
> www.oraclewizard.com <http://www.oraclewizard.com>
> (c) 571.276.4790
> Twitter: _at_YourNavionPilot
>
> --
> http://www.freelists.org/webpage/oracle-l
> <http://www.freelists.org/webpage/oracle-l>
>
>
>

-- 
================================
"Rules are for the obedience of fools and the guidance of wise men" -Douglas Bader
Robert P. Lockard, Oracle ACE
www.oraclewizard.com
(c) 571.276.4790
Twitter: _at_YourNavionPilot


--
http://www.freelists.org/webpage/oracle-l