SplitMeasureRanges.sql

-- Create measure_range table - same structure as measure_ranges table
CREATE TABLE IF NOT EXISTS `measure_range` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `measure_id` int(10) unsigned NOT NULL,
  `age_min` int(10) unsigned DEFAULT NULL,
  `age_max` int(10) unsigned DEFAULT NULL,
  `gender` tinyint(3) unsigned DEFAULT NULL,
  `range_lower` decimal(7,3) DEFAULT NULL,
  `range_upper` decimal(7,3) DEFAULT NULL,
  `alphanumeric` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `interpretation` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);
-- Insert data from blis_301
INSERT INTO iblis.measure_range(measure_id, age_min, age_max, gender, range_lower, range_upper) 
SELECT measure_id, age_min, age_max, case sex when 'M' then 0 when 'F' then 1 when 'B' then 2 end as sex, range_lower, range_upper 
FROM blis_301.reference_range where blis_301.reference_range.measure_id IN (select distinct(measure_id) from blis_301.measure);
-- Create splitter function and procedure to move data from measure_range to measure_ranges table
DELIMITER $$
 
CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER) 
RETURNS VARCHAR(65000)
BEGIN
  DECLARE output VARCHAR(65000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END $$

CREATE PROCEDURE MeasureRanges2Alphanumeric()
BEGIN
  DECLARE i INTEGER;
 
  SET i = 1;
  REPEAT
    INSERT INTO measure_ranges (measure_id, alphanumeric)
      SELECT measure_id, strSplit(alphanumeric, '/', i) FROM measure_range
      WHERE strSplit(alphanumeric, '/', i) IS NOT NULL ORDER BY measure_id ASC;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
END $$
DELIMITER ;
-- Call the procedure
CALL MeasureRanges2Alphanumeric();
-- Drop measure_range table
DROP TABLE iblis.measure_range;