locked
Need a comma separated function in mysql RRS feed

  • Question

  • User1182241144 posted

    Can anyone please provide me comma seprated split  function in mysql.

    Thursday, August 23, 2012 8:42 AM

Answers

  • User-578610739 posted

    Hi Shivani,

    see link,

    http://stackoverflow.com/questions/662207/mysql-results-as-comma-separated-list

    http://stackoverflow.com/questions/2182668/how-to-split-comma-separated-text-in-mysql-stored-procedure

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 24, 2012 1:51 AM

All replies

  • User1182241144 posted

    DROP FUNCTION IF EXISTS splitAndTranslate
    CREATE FUNCTION splitAndTranslate(str TEXT, delim VARCHAR(124))
    RETURNS TEXT
    DETERMINISTIC
    BEGIN
    DECLARE i INT DEFAULT 0; -- total number of delimiters
    DECLARE ctr INT DEFAULT 0; -- counter for the loop
    DECLARE str_len INT; -- string length,self explanatory
    DECLARE out_str text DEFAULT ''; -- return string holder
    DECLARE temp_str text DEFAULT ''; -- temporary string holder
    DECLARE temp_val VARCHAR(255) DEFAULT ''; -- temporary string holder for query

    -- get length
    SET str_len=LENGTH(str);

    SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, '')))/LENGTH(delim) + 1;
    -- get total number delimeters and add 1
    -- add 1 since total separated values are 1 more than the number of delimiters

    -- start of while loop
    WHILE(ctr<i) DO
    -- add 1 to the counter, which will also be used to get the value of the string
    SET ctr=ctr+1;

    -- get value separated by delimiter using ctr as the index
    SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr - 1)) + 1), delim, '');

    -- query real value and insert into temporary value holder, temp_str contains the exploded ID
    SELECT KPITypeName INTO temp_val FROM KpiTypes WHERE KpiTypeId=temp_str;

    -- concat real value into output string separated by delimiter
    SET out_str=CONCAT(out_str, temp_val, ',');
    END WHILE;
    -- end of while loop

    -- trim delimiter from end of string
    SET out_str=TRIM(TRAILING delim FROM out_str);
    RETURN(out_str); -- return

    END

    I try to create this function but its giving me 1064 error

    Thursday, August 23, 2012 11:19 PM
  • User-578610739 posted

    Hi Shivani,

    see link,

    http://stackoverflow.com/questions/662207/mysql-results-as-comma-separated-list

    http://stackoverflow.com/questions/2182668/how-to-split-comma-separated-text-in-mysql-stored-procedure

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 24, 2012 1:51 AM