How to rename each column values with distinct prime number?

[Amad Khan]

Hello Every body:

i want rename a table values in bulk as...each colum value(cell) is assing a unique value...
For example:

Table looks like

column1, column2,column3,column4
Milk,sugar,butter,cheez
null,sugar,butter,null

now when i run the procedure...
it will change my table values as

column1, column2,column3,column4
2,3,4,5
0,2,3,0

i-2 replacing each coloumn value with a unique prime value...

thnx BR amad...

[amitpatel66]

Try this:

SQL> ed
Wrote file afiedt.buf
 
  1  with t as (SELECT 'MILK' col1,'SUGAR' col2,'BUTTER' col3,'CHEESE' col4 from dual
  2  union select NULL,'SUGAR','BUTTER',NULL from dual),
  3  s as (SELECT col1,(CASE WHEN col1 IS NULL THEN 0 ELSE ROW_NUMBER() OVER(ORDER BY col1) END ) rn
  4  (SELECT col1 from t
  5  UNION
  6  select col2 from t
  7  UNION
  8  SELECT col3 from t
  9  union
 10  select col4 from t))
 11  select nvl(C1,0),nvl(c2,0),nvl(c3,0),nvl(c4,0) FROM
 12  (SELECT (SELECT rn FROM s WHERE col1 = x.col1) c1,
 13  (SELECT rn FROM s WHERE col1 = x.col2) c2,
 14  (SELECT rn FROM s WHERE col1 = x.col3) c3,
 15  (SELECT rn FROM s WHERE col1 = x.col4) c4
 16* FROM t x)
SQL> /
 
 NVL(C1,0)  NVL(C2,0)  NVL(C3,0)  NVL(C4,0)
---------- ---------- ---------- ----------
         3          4          1          2
         0          4          1          0
 
SQL> 
 

[Rabbit]

4 is not a prime number.

[amitpatel66]

I just missed that it has to be a prime number. In that case you will need to store the list of prime numbers seperately and associate those with the value. Else if single query is built to generate prime numbers as well, it would be not so good performance wise.