if else for loop split/concat string advanced method in mysql

[Duy Dinh]

drop function if exists SPLIT_STR;
delimiter $$
CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
$$
delimiter ;

DROP FUNCTION IF EXISTS getCIDs2011fromCIDs2012;
delimiter $$

CREATE FUNCTION getCIDs2011fromCIDs2012(cids MEDIUMTEXT) RETURNS MEDIUMTEXT
BEGIN
DECLARE s mediumtext; -- result
DECLARE size INT; -- number of CIDs in the set
DECLARE cid1, cid2 INT;
DECLARE i INT;

SET size=1 + LENGTH(cids) - LENGTH(REPLACE(cids, '|', ''));


SET cid1 = SPLIT_STR(cids, '|', 1);
SET cid2 = GETCID2011FROMCID2012(cid1);
SET s=cid2;
SET i=2;
LOOPOVERCIDS: LOOP

    IF i <= size THEN
        SET cid1 = SPLIT_STR(cids, '|', i);
        SET cid2 = GETCID2011FROMCID2012(cid1);
        SET s = CONCAT(s, "|", cid2);
        SET i=i+1;      
    ELSE
        LEAVE LOOPOVERCIDS;
    END IF;
END LOOP LOOPOVERCIDS;
RETURN s;

END
$$
delimiter ;
-- SELECT GETCIDS2011FROMCIDS2012('2|15|10|15');

[Duy Dinh]

/***
This script is used to extract some statistical information of ICD9-CM

@author: Duy Dinh, CRP Henri Tudor, Luxembourg
@date: 07 May 2013

*/
USE icd9cm;

DROP TABLE IF EXISTS relationships_is_a_2008;
delimiter $$

CREATE TABLE relationships_is_a_2008 (
ID INT NOT NULL AUTO_INCREMENT,
CID1 INT(11) NOT NULL,
CODE1 VARCHAR(20) NOT NULL,
CHILDREN VARCHAR(100),
CHILDREN_COUNT INT,
ATTRIBUTE_COUNT INT,
PRIMARY KEY (ID)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8$$

delimiter ;

-- populate table with original codes
INSERT INTO relationships_is_a_2008(CID1, CODE1)
(
SELECT ID, code
FROM codes_2008
);

SELECT * FROM relationships_is_a_2008;

DROP FUNCTION IF EXISTS getChildrenConcepts_2008;
delimiter $$
CREATE FUNCTION getChildrenConcepts_2008(CUI VARCHAR(10)) RETURNS VARCHAR(20)
BEGIN
DECLARE s VARCHAR(20);
SELECT GROUP_CONCAT(ID SEPARATOR "|") INTO S FROM codes_2008
WHERE code LIKE CONCAT(CUI, '.%'); # code starts with 'CUI.'
RETURN s;
END
$$
delimiter ;

-- SELECT getChildrenConcepts_2008('001');

DROP FUNCTION IF EXISTS getChildrenConcepts_Count_2008;
-- get all attributes of a given ICD concepts
delimiter $$
CREATE FUNCTION getChildrenConcepts_Count_2008(CUI VARCHAR(10)) RETURNS INT
BEGIN
DECLARE counter INT;
SELECT COUNT(*) INTO COUNTER FROM codes_2008
WHERE code LIKE CONCAT(CUI, '.%'); # code starts with 'CUI.'
RETURN counter;
END
$$
delimiter ;

-- SELECT getChildrenConcepts_Count_2008('001');

DROP FUNCTION IF EXISTS getAttributes_Count_2008;
delimiter $$
CREATE FUNCTION getAttributes_Count_2008(CID INT) RETURNS INT
BEGIN
DECLARE s MEDIUMTEXT; -- result
DECLARE size INT; -- number of CIDs in the set

#select concat_ws('|', title, notes, includes, excludes) 
SELECT CONCAT_WS('|', title, notes, includes, excludes) 
INTO s FROM codes_2008
WHERE `ID` = CID;   
# Replace semi vertical bar by the full vertical bar
SELECT REPLACE(s, '¦', '|') into S;
# count the number of '|' in s
SET size=1 + LENGTH(s) - LENGTH(REPLACE(s, '|', ''));
RETURN size;

END
$$
delimiter ;

UPDATE codes_2008
SET excludes = IF (excludes = "", NULL, TRIM(excludes)),
includes = IF (includes = "", NULL, TRIM(includes)),
notes = IF (notes = "", NULL, TRIM(notes));

-- SELECT getAttributes_Count_2008(1);

-- update table with parent concepts
UPDATE relationships_is_a_2008
SET CHILDREN = GETCHILDRENCONCEPTS_2008(code1),
CHILDREN_COUNT = GETCHILDRENCONCEPTS_COUNT_2008(code1),
ATTRIBUTE_COUNT = GETATTRIBUTES_COUNT_2008(CID1);

SELECT * FROM relationships_is_a_2008;

-- -- count the number of is_a relationships
-- select sum(children_count) from relationships_is_a_2008;
-- select sum(children_count) from relationships_is_a_2009;
-- select sum(children_count) from relationships_is_a_2010;
-- select sum(children_count) from relationships_is_a_2011;
--
-- -- the number of concepts
-- select count() from codes_2008;
-- select count() from codes_2009;
-- select count() from codes_2010;
-- select count() from codes_2011;