Home > Oracle, SQL > Fun with XOR

Fun with XOR


This story begins with a puzzling question asked in one of the OTN forums: “I need some help to know what’s the auxiliary constructs, and examples in the auxiliary constructs.” Eventually the question was traced to the following SQL problem posted on the University of Freiburg’s website:

“Exchange the members of NATO and EU without introducing auxiliary constructs (tables, views, auxiliary entries, PL/SQL). Hint: compute the new value of the attribute Organization as a function from the original one. Note: do not use Oracle’s DECODE-function.”

In another version of the problem, there were additional prohibitions: “DECODE, CASE, REGEXP_REPLACE etc. are not allowed.”

The table required for the exercise is from the Mondial database:

CREATE TABLE isMember
(Country VARCHAR2(4),
 organization VARCHAR2(12),
 Type VARCHAR2(35) DEFAULT 'member',
 CONSTRAINT MemberKey PRIMARY KEY (Country,organization) );

Here is the data for the problem:

INSERT INTO isMember VALUES ('GR','NATO','member');
INSERT INTO isMember VALUES ('F','NATO','member');
INSERT INTO isMember VALUES ('E','NATO','member');
INSERT INTO isMember VALUES ('D','NATO','member');
INSERT INTO isMember VALUES ('I','NATO','member');
INSERT INTO isMember VALUES ('B','NATO','member');
INSERT INTO isMember VALUES ('L','NATO','member');
INSERT INTO isMember VALUES ('NL','NATO','member');
INSERT INTO isMember VALUES ('TR','NATO','member');
INSERT INTO isMember VALUES ('DK','NATO','member');
INSERT INTO isMember VALUES ('N','NATO','member');
INSERT INTO isMember VALUES ('IS','NATO','member');
INSERT INTO isMember VALUES ('P','NATO','member');
INSERT INTO isMember VALUES ('GB','NATO','member');
INSERT INTO isMember VALUES ('CDN','NATO','member');
INSERT INTO isMember VALUES ('USA','NATO','member');

INSERT INTO isMember VALUES ('GR','EU','member');
INSERT INTO isMember VALUES ('F','EU','member');
INSERT INTO isMember VALUES ('E','EU','member');
INSERT INTO isMember VALUES ('A','EU','member');
INSERT INTO isMember VALUES ('CZ','EU','member');
INSERT INTO isMember VALUES ('D','EU','member');
INSERT INTO isMember VALUES ('H','EU','member');
INSERT INTO isMember VALUES ('I','EU','member');
INSERT INTO isMember VALUES ('SK','EU','member');
INSERT INTO isMember VALUES ('SLO','EU','member');
INSERT INTO isMember VALUES ('LV','EU','member');
INSERT INTO isMember VALUES ('LT','EU','member');
INSERT INTO isMember VALUES ('PL','EU','member');
INSERT INTO isMember VALUES ('B','EU','member');
INSERT INTO isMember VALUES ('L','EU','member');
INSERT INTO isMember VALUES ('NL','EU','member');
INSERT INTO isMember VALUES ('BG','EU','member');
INSERT INTO isMember VALUES ('RO','EU','member');
INSERT INTO isMember VALUES ('DK','EU','member');
INSERT INTO isMember VALUES ('EW','EU','member');
INSERT INTO isMember VALUES ('SF','EU','member');
INSERT INTO isMember VALUES ('S','EU','member');
INSERT INTO isMember VALUES ('IRL','EU','member');
INSERT INTO isMember VALUES ('M','EU','member');
INSERT INTO isMember VALUES ('P','EU','member');
INSERT INTO isMember VALUES ('GB','EU','member');
INSERT INTO isMember VALUES ('CY','EU','member');
INSERT INTO isMember VALUES ('MK','EU','membership applicant');
INSERT INTO isMember VALUES ('HR','EU','membership applicant');
INSERT INTO isMember VALUES ('TR','EU','membership applicant');

Here’s my solution. It makes use of the properties of the XOR operation. Oracle provides the bit_XOR function for the purpose.

p XOR p = 0
p XOR q = q XOR p
(p XOR q) XOR r = p XOR (q XOR r)

('EU  ' XOR 'EU  ') XOR 'NATO' = 'NATO'
('NATO' XOR 'EU  ') XOR 'NATO' = 'EU  '

UPDATE isMember
SET organization =
  RTRIM
  (
    UTL_RAW.cast_to_varchar2
    (
      UTL_RAW.bit_xor
      (
        UTL_RAW.bit_xor
        (
          UTL_RAW.cast_to_raw (RPAD(organization, 4)),
          UTL_RAW.cast_to_raw ('EU  ')
        ),
        UTL_RAW.cast_to_raw ('NATO')
      )
    )
  )
WHERE organization IN ('EU', 'NATO');

As with the First International NoCOUG SQL Challenge, I’m sure there are other solutions.

Categories: Oracle, SQL
  1. November 10, 2009 at 4:48 pm

    update ismember target
    set target.organization =
    (
    select y.tobe
    from
    (
    select o
    , nvl(lead(o) over (order by o)
    , lag(o) over (order by o)) tobe
    from
    (
    select distinct organization o
    from ismember
    where organization IN( ‘EU’, ‘NATO’ )
    )
    ) y
    where target.organization=y.o
    )
    ;

  2. November 11, 2009 at 3:09 am

    update ismember set organization=replace(‘EUNATO’,organization);

  3. Iggy Fernandez
    November 11, 2009 at 7:36 am

    Laurent Schneider is the author of Advanced Oracle SQL Programming: The Expert Guide to Writing Complex Queries.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: