scramble data

use translate function http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions196.htm#i1501659 or replace functionhttp://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions134.htm#i78608

update for translate can help if u have  as second
thanks

if you like the select then turn it into an update with


update aa
set user_name =  CASE
           WHEN INSTR(user_name, 'a') = 0
               THEN user_name
           ELSE    SUBSTR(user_name, 1, INSTR(user_name, 'a') - 1)
                || 'zp'
                || SUBSTR(user_name, INSTR(user_name, 'a') + 1)
       END
where user_name like '%a%'

whenever you use '%a%', you should always use like operator because that is the pattern matching operator ( should not use = )

UPDATE aa
SET user_name = REPLACE(user_name, 'a','zp')
WHERE user_name LIKE '%a%'

You can't use
user_name = REPLACE(user_name, 'a','zp')
because that will replace all "a" with "zp" but from the example the author is replacing
only the first instance of "a" in the string.

This replaces the first "a" with "zp" and returns the rest unchanged.

update aa
set user_name =  CASE
           WHEN INSTR(user_name, 'a') = 0
               THEN user_name
           ELSE    SUBSTR(user_name, 1, INSTR(user_name, 'a') - 1)
                || 'zp'
                || SUBSTR(user_name, INSTR(user_name, 'a') + 1)
       END
where user_name like '%a%'

hi sd,
in this case scramble1 and scramble2 gves same result

thanks

SELECT user_name, REPLACE(user_name, 'a', 'zp') scramble1,
       CASE
           WHEN INSTR(user_name, 'a') = 0
               THEN user_name
           ELSE    SUBSTR(user_name, 1, INSTR(user_name, 'a') - 1)
                || 'zp'
                || SUBSTR(user_name, INSTR(user_name, 'a') + 1)
       END scramble2
  FROM (SELECT 'joe Patricia' user_name
          FROM DUAL
        UNION ALL
        SELECT 'mary hill' user_name
          FROM DUAL) aa

scramble1 and scramble2 don't give the same result when there is more than one "a"
That's the point of making scramble2.
From your example data above,  only the first "a" in Patricia is scrabmled to "zp"
mary hill scrambles the same in both cases but that's obviously correct.
There is only one "a" in the whole name so what difference could there be?

USER_NAME    SCRAMBLE1                SCRAMBLE2                
------------ ------------------------ --------------------------
joe Patricia joe Pzptricizp           joe Pzptricia            
mary hill    mzpry hill               mzpry hill                

If my answers in SCRAMBLE2 aren't what you're looking for.  Then I think you need to restate the problem.  Because they match exactly what you showed in your question.
But SCRAMBLE1 does not.

you are rite but they want ro do sumthing like
if data type is varchar then to_char(rownum) works
but not for data type number
wht should i do there ?

update dmt_customers_dm
set
CUSTOMER_NAME = 'CUSTOMER_NAME'||to_char(rownum),
SYNTH_CUST_NO = 'SYNTH_CUST_NO'||to_char(rownum),
CUSTOMER_NAME_PHONETIC = 'CUST_NAME'||to_char(rownum),
URL= 'url'||to_char(rownum),

Is this what ur trying do?

update dmt_customers_dm
set CUSTOMER_NAME = CUSTOMER_NAME || to_char(rownum),
SYNTH_CUST_NO = SYNTH_CUST_NO || to_char(rownum),
CUSTOMER_NAME_PHONETIC = CUSTOMER_NAME_PHONETIC || to_char(rownum),
URL = URL || to_char(rownum);

you can't simply append or prepend a number because it'll still be a number.
if your number has a known range you can do something fixed-format.

if, for instance your number X has a range of 1-1,000,000

then you can do

X = (rownum *10000000) + X   so you prepend your number with 10,000,000 times the rownum.

or you can turn your number into a character and store in a different field.

Y = to_char(x) || '_' || to_char(rownum)

None of this is really scrambling data though.  The replace function is the best for doing that.

This numbering thing seems like a completely different topic.  Is this a separate question?

or, if you really don't care at all about preserving the original values.

You can simply set X = rownum

sd its same question well what happen is if i use replace it will take long time as
we have like 50 columns to scramble so i guess this is better way

replace is a fast operation.  Anything you do to 50 columns will take a little time, replace is about as fast you're going to get probably.

Something else to consider is that if you are appending data to your varchar2 columns you'll make your data grow and may start migrating or chaining.  Which not only takes more space but more time to do the updates since you have to allocate more space and more time to do queries since now your data is spread across multiple blocks.