CREATE TABLE TEAMS_NEW
(TEAMNO INTEGER NOT NULL PRIMARY KEY,
EmployeeNO INTEGER NOT NULL,
DIVISION SET ('first','second','third','fourth'));
INSERT INTO TEAMS_NEW VALUES (1, 27, 'first')
;
INSERT INTO TEAMS_NEW VALUES (2, 27, 'first,third')
;
INSERT INTO TEAMS_NEW VALUES (3, 27, 'first,third,sixth')
;
INSERT INTO TEAMS_NEW VALUES (4, 27, 'first,fifth')
;
INSERT INTO TEAMS_NEW VALUES (5, 27, NULL)
;
INSERT INTO TEAMS_NEW VALUES (6, 27, 7)
;
INSERT INTO TEAMS_NEW VALUES (7, 27, CONV(1001,2,10))
;
SELECT * FROM TEAMS_NEW;
SELECT TEAMNO, DIVISION
FROM TEAMS_NEW
WHERE DIVISION & POWER(2,3-1) = POWER(2,3-1)
;
delimiter $$
CREATE FUNCTION POSITION_IN_SET
(P_COLUMN BIGINT, POSITION SMALLINT)
RETURNS BOOLEAN
BEGIN
RETURN (P_COLUMN & POWER(2, POSITION-1) =
POWER(2,POSITION-1));
END$$
delimiter ;
SELECT TEAMNO, DIVISION
FROM TEAMS_NEW
WHERE POSITION_IN_SET(DIVISION, 3);
drop table teams_new;
|