CODEWITHSUNDEEP
sqloracle-database
N Din
N Din

Reputation: 40

How to update field value by determining each digit in field?

Although I saw update statements to update field based on existing values, I could not find anything similar to this scenario:

Suppose you have a table with only one column of number(4) type. The value in the first record is 1010.

create table stab(
  nmbr number(4)
);

insert into stab values(1010);

For each digit
   When the digit is 1 -- add 3 to the digit
   When the digit is 0 -- add four to the digit
end

This operations needs to be completed in a single statement without using pl/sql.

I think substr function need to be used but don't know how to go about completing this.

Thanks in advance.

Upvotes: 1

Views: 99

Answers (4)

Roger Cornejo
Roger Cornejo

Reputation: 1547

My answer to the interview question would have been that the DB design violates the rules of normalization (i.e. a bad design) and would not have this kind of "update anomaly" if it were properly designed. Having said that, it can easily be done with an expression using various combinations of single row functions combined with the required arithmetic operations.

Upvotes: 0

Gajoseph
Gajoseph

Reputation: 31

assuming its always a 4 digit #; you could use substring like below -- postgres SQL example

SELECT CASE
         WHEN a = 0 THEN a + 4
         ELSE a + 3
       end AS a,
       CASE
         WHEN b = 0 THEN b + 4
         ELSE b + 3
       end AS b,
       CASE
         WHEN c = 0 THEN c + 4
         ELSE c + 3
       end AS c,
       CASE
         WHEN d = 0 THEN d + 4
         ELSE c + 3
       end AS d
FROM   ( SELECT Substr( '1010', 1, 1 ) :: INT AS a,
                Substr( '1010', 2, 1 ) :: INT b,
                Substr( '1010', 3, 1 ) :: INT c,
                Substr( '1010', 4, 1 ) :: INT d )a

--- Other option may be (tried in postgreSQL :) ) to split the number using regexp_split_to_table into rows;then add individual each digit based on the case statement and then concat the digits back into a string 

SELECT array_to_string ( array
       (
              select
                     case
                            WHEN val = 0 THEN val +4
                            ELSE val +3
                     END
              FROM   (
                            SELECT regexp_split_to_table ( '101010','' ) ::INT val
                     ) a
       ) ,'' )

Upvotes: 0

user5683823
user5683823

Reputation:

So, it seems you need to convert every 0 and 1 to a 4, and leave all the other digits alone. This seems like a string operation (and the reference to "digits" itself suggests the same thing). So, convert the number to a string, use the Oracle TRANSLATE function (see the documentation), and convert back to number.

update stab
set    nmbr = to_number(translate(to_char(nmbr, '9999'), '01', '44'))
;

Upvotes: 1

SandPiper
SandPiper

Reputation: 2906

SELECT DECODE(SUBSTR(nmbr,1,1), '1', 1 + 3, '0', 0 + 4) AS Decoded_Nmbr
FROM stab
ORDER BY Decoded_Nmbr

Is that what you are after?

Upvotes: 2

Related Questions