Re: Split String
Posted by:
jim smith
Date: January 14, 2009 01:40PM
I did one like so that helped me prepare a dynamic section in my where clause:
DELIMITER $$;
DROP FUNCTION IF EXISTS `apache_logs`.`split`$$
CREATE DEFINER=`User`@`localhost` FUNCTION `split`(sStringIn text,splitChar varchar(1)) RETURNS text CHARSET latin1
NO SQL
BEGIN
DECLARE comma INT DEFAULT 0;
DECLARE mylist TEXT DEFAULT sStringIn;
DECLARE temp TEXT DEFAULT '';
DECLARE strlen int DEFAULT LENGTH(sStringIn);
DECLARE outtext text(10000) DEFAULT '';
/* find the first instance of the spliting character */
SET comma = LOCATE(splitChar,mylist);
/* Insert each split variable into the temp table */
set outtext='';
WHILE strlen > 0 DO
IF comma = 0 THEN
SET temp = TRIM(mylist);
SET mylist = '';
SET strlen = 0;
END IF;
IF comma != 0 THEN
SET temp = TRIM(SUBSTRING(mylist,1,comma-1));
SET mylist = TRIM(SUBSTRING(mylist FROM comma+1));
SET strlen = LENGTH(mylist);
END IF;
IF temp != ''
THEN
/* This is the magic line and can be converted to whatever you need */
set outtext = concat(outtext,' and Path not like ',char(39),char(37),temp,char(37),char(39));
END IF;
SET comma = LOCATE(splitChar,mylist);
END WHILE;
return outtext;
END$$
DELIMITER ;$$
Subject
Written By
Posted
December 17, 2007 09:46AM
February 20, 2008 12:29PM
Re: Split String
January 14, 2009 01:40PM
September 08, 2008 04:27AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.