Thursday, June 4, 2009

Split Values in My Sql with using store procedure

Hello, here i m going to describe the split value function /Store procedure in mysql....

suppose i want to pass mulitple id as '12,113,14,15,10' than how can i separate.... the solution is below....use below store procedure to separate each id's

DELIMITER $$  
DROP PROCEDURE IF EXISTS `ecommerce`.`SplitValue`$$  
CREATE  PROCEDURE `SplitValue`
(    
    input VARCHAR(8000),    
    delim VARCHAR(10),    
    Ids VARCHAR(36) 
) 
BEGIN 
DECLARE foundPos TINYINT UNSIGNED; 
DECLARE tmpTxt TEXT; 
DECLARE delimLen TINYINT UNSIGNED; 
DECLARE element TEXT; 
DROP TEMPORARY TABLE IF EXISTS tmpValues;  
CREATE TEMPORARY TABLE tmpValues 
(    
    valuess DECIMAL(18,0) NOT NULL DEFAULT 0,    
    Id VARCHAR(36) 
) 
ENGINE = MEMORY; 

SET delimLen = LENGTH(delim); 
SET tmpTxt = input; 
SET foundPos = INSTR(tmpTxt,delim); 

WHILE foundPos <> 0 
DO    
    SET element = SUBSTRING(tmpTxt, 1, foundPos-1);    
    SET tmpTxt = REPLACE(tmpTxt, CONCAT(element,delim), '');    
    SET element=CAST(element AS DECIMAL(18,0));    
    INSERT INTO tmpValues (valuess,Id) VALUES ( element,Ids);    
    SET foundPos = INSTR(tmpTxt,delim);   
END WHILE; 
IF tmpTxt <> '' 
THEN    
    SET tmpTxt=CAST(tmpTxt AS DECIMAL(18,0));    
    INSERT INTO tmpValues (valuess,Id)    
    VALUES (tmpTxt,Ids); 
END IF;  
SELECT Id,valuess FROM tmpValues;  
END$$  DELIMITER ;   
   

suppose i m calling this store procedure as

CALL SplitValue('11,12,13,14,15') 

-- then the oputput of this 
-- id VALUE 
-- 1  11 
-- 2  12 
-- 3  13 
-- 4  14 
-- 5  15

No comments:

Post a Comment

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...