Re: DOUBT - Oracle command to REPLACE data

I'd second Robert on this, this seems to me a very weak design - with 45 bits you are far from what Oracle can store:

SQL> set num 40
SQL> select power(2, 46) - 1 from dual;

                POWER(2,46)-1
----------------------------------------
               70368744177663

SQL> Handling integers would allow you to use the bit functions and it's always possible to "translate" them to a binary-looking string.

Alternatively, something like this could give you some ideas if you are powerless on the design:

select val
from (select rn, replace(sys_connect_by_path(bit, '/'), '/', '') val

       from (select rn,
                    greatest(to_number(substr(val, rn, 1)),
                             to_number(substr(val2, rn, 1))) bit
             from (select 
'111110000000000000000000000000000000000000000' val from dual)
                   cross join (select 
'000000000000000000000000000000101010101010100' val2 from dual)
                   cross join (select rownum rn
                              from dual
                              connect by level <= 45))
       connect by rn = prior rn + 1
       start with rn = 1)

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


On 07/02/2011 11:20 PM, Robert Freeman wrote:


> Are you trying to do the equivalent of bitwise operations here? If so, 

> I'd change your approach altogether and use actually bit manipulations 

> using the functions that Oracle provides. I've used them for 

> security/role/grouping designs quite successfully in the past and it's 

> much faster than what you may be trying to do.

>

> Robert

>

> Robert G. Freeman

> Master Principal Consultant, Oracle Corporation, Oracle ACE

> Author of various books on RMAN, New Features and this shorter 

> signature line.

> Blog: http://robertgfreeman.blogspot.com

>

> Note: THIS EMAIL IS NOT AN OFFICIAL ORACLE SUPPORT COMMUNICATION. It 

> is just the opinion of one Oracle employee. I can be wrong, have been 

> wrong in the past and will be wrong in the future. If your problem is 

> a critical production problem, you should always contact Oracle 

> support for assistance. Statements in this email in no way represent 

> Oracle Corporation or any subsidiaries and reflect only the opinion of 

> the author of this email.

>

>

> ------------------------------------------------------------------------

> *From:* Eriovaldo Andrietta <ecandrietta_at_gmail.com>

> *To:* ORACLE-L <oracle-l_at_freelists.org>

> *Sent:* Sat, July 2, 2011 12:46:21 PM

> *Subject:* DOUBT - Oracle command to REPLACE data

>

> Hi Friends,

>

>    I have the following issue:

>

>    I need to store a column VARCHAR2(45).

>

>    If I am inserting data for first time I can , for example,  save 

> something like this:

>

>       111110000000000000000000000000000000000000000

>

>    When the line exists recorded in the table with the content above, 

> and during my process I must updated the following, considering only 

> character 1:

>

>       000000000000000000000000000000101010101010100

>

>    The final result must be as below, replacing only the character 1:

>

> 111110000000000000000000000000101010101010100

>   I know that I can do it:

>      WHILE X < Y

>        LOOP

>          FINAL COLUMN := SUBSTR(N) || 1 || ....

>       END LOOP;

>

>  But is there any other way to do it, like regular expression or 

> another command / instruction ?

>

> Regards

> Eriovaldo

>

>




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