Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Question Unanswered: How Do You Parse A String in MySQL???

    HI

    I have a stored procedure which needs to be executed as two transactions or roll back if unsuccessful. However I have an input parameter called AccreditationIds which is in the format of a string e.g. ("1,2,4,6,8,..."). Each number in this string represent the primary key of a table called tbl_accreditations see below:

    Code:
    CREATE TABLE `tbl_accreditations` (
      `accreditation_id` int(10) unsigned NOT NULL auto_increment,
      `accreditation_name` varchar(100) NOT NULL,
      PRIMARY KEY  (`accreditation_id`),
      UNIQUE KEY `accreditation_name` (`accreditation_name`)
    ) ENGINE=InnoDB

    the jobseeker details are stored in a table called tbl_jobeekers. Since a jobseeker can have multiple accreditations this info is stored in many to many table called tbl_jobseeker_accreditions - below:

    HTML Code:
    CREATE TABLE `tbl_jobseeker_accreditations` (
      `accreditation_id` int(10) unsigned NOT NULL,
      `js_id` int(10) unsigned NOT NULL,
      PRIMARY KEY  (`accreditation_id`,`js_id`),
      FOREIGN KEY (`accreditation_id`) REFERENCES `tbl_accreditations` (`accreditation_id`),
      FOREIGN KEY (`js_id`) REFERENCES `tbl_jobseekers` (`js_id`) ON DELETE CASCADE
    ) ENGINE=InnoDB

    Now the question is how do I parse this AccreditationIds string within a stored procedure and add a records to the tbl_jobseeker_accreditations? I have the procedure as below but unsure how to parse the AccreditationIds string and to iterate it

    Code:
    CREATE PROCEDURE jobseeker_add(IN inFname VARCHAR(50),IN inLname VARCHAR(50),
                                   IN inAddLine VARCHAR(100), IN inCity VARCHAR(50),
                                   IN inZip VARCHAR(50),IN inCountryId INT,
                                   IN inPhone VARCHAR(50),IN inEmail VARCHAR(100),
                                   IN inPwd VARCHAR(50), IN inAccreditationIds VARCHAR(50))
    
    DECLARE jobseekerId INT;
    
    
    BEGIN
    start transaction; 
        -- Insert a new record into jobseeker and obtain the new jobseeker ID
        INSERT INTO tbl_jobseekers (js_fname, js_lname, js_addressline, 
    	                        js_city, js_state, js_zip, js_country_id, 
                                    js_phone, js_email,  js_pwd) 
              VALUES (inFname, inLname, inAddLine, inAddLine2 ,inCity, inZip,
                      inCountryId, inPhone, inEmail, inPwd);
    		
        -- Obtain the new jobseeker ID
        SELECT LAST_INSERT_ID() INTO jobseekerId;
    		
        -- unsure how to parse the inAccreditationIds
        WHILE inAccreditationIds
             INSERT INTO tbl_accreditations (accreditation_id, js_id)
                  VALUES (inAccreditationIds, jobseekerId);
        END WHILE
    commit;		
    END
    Any suggestions?
    Last edited by ozzii; 03-30-09 at 17:08.

  2. #2
    Join Date
    Mar 2007
    Posts
    212
    OK I've done a bit of my own research and I've found the following function. Unfortunately its in T-SQL. Any body know if this would work in MySQL and how it could be implemented for the above scenario?

    Code:
    CREATE FUNCTION dbo.Parse ( @Array VARCHAR(1000), @separator VARCHAR(10)) 
    RETURNS @resultTable TABLE 
    	(parseValue VARCHAR(100))
    AS
    BEGIN
    
    	DECLARE @separator_position INT 
    	DECLARE @array_value VARCHAR(1000) 
    	
    	SET @array = @array + @separator
    	
    	WHILE patindex('%' + @separator + '%' , @array) <> 0 
    	BEGIN
    	
    	  SELECT @separator_position =  patindex('%' + @separator + '%', @array)
    	  SELECT @array_value = left(@array, @separator_position - 1)
    	
    		INSERT @resultTable
    		VALUES (Cast(@array_value AS varchar))
    
    	  SELECT @array = stuff(@array, 1, @separator_position, '')
    	END
    
    	RETURN
    END

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    OK I've done a bit of my own research and I've found the following function. Unfortunately its in T-SQL. Any body know if this would work in MySQL and how it could be implemented for the above scenario?

    Code:
    CREATE FUNCTION dbo.Parse ( @Array VARCHAR(1000), @separator VARCHAR(10)) 
    RETURNS @resultTable TABLE 
    	(parseValue VARCHAR(100))
    AS
    BEGIN
    
    	DECLARE @separator_position INT 
    	DECLARE @array_value VARCHAR(1000) 
    	
    	SET @array = @array + @separator
    	
    	WHILE patindex('%' + @separator + '%' , @array) <> 0 
    	BEGIN
    	
    	  SELECT @separator_position =  patindex('%' + @separator + '%', @array)
    	  SELECT @array_value = left(@array, @separator_position - 1)
    	
    		INSERT @resultTable
    		VALUES (Cast(@array_value AS varchar))
    
    	  SELECT @array = stuff(@array, 1, @separator_position, '')
    	END
    
    	RETURN
    END

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Ok i've managed to knock up something to split the string and iterate it. See below. Does any body have a solution so that it could be implemented as a function instead of having to rewrite it each time you need to split a string to do multiple inserts?

    Code:
    CREATE PROCEDURE jobseeker_add(IN inFname VARCHAR(50),IN inLname VARCHAR(50),
                                   IN inAddLine VARCHAR(100), IN inCity VARCHAR(50),
                                   IN inZip VARCHAR(50),IN inCountryId INT,
                                   IN inPhone VARCHAR(50),IN inEmail VARCHAR(100),
                                   IN inPwd VARCHAR(50), IN inAccreditationIds VARCHAR(50))
    
    DECLARE jobseekerId INT;
    DECLARE delim VARCHAR(1);
    DECLARE foundDelimPos tinyint unsigned;
    DECLARE tmpStr VARCHAR(100);
    DECLARE element VARCHAR(100);
    
    
    BEGIN
    start transaction; 
        -- Insert a new record into jobseeker and obtain the new jobseeker ID
        INSERT INTO tbl_jobseekers (js_fname, js_lname, js_addressline, 
    	                        js_city, js_state, js_zip, js_country_id, 
                                    js_phone, js_email,  js_pwd) 
              VALUES (inFname, inLname, inAddLine, inAddLine2 ,inCity, inZip,
                      inCountryId, inPhone, inEmail, inPwd);
    		
        -- Obtain the new jobseeker ID
        SELECT LAST_INSERT_ID() INTO jobseekerId;
    		
        -- assign accreditations string to temp string e.g. ("1,2,3,4,5")
        SET tmpStr = inAccreditationIds;
    
        -- set delimiter as a comma
        SET delim = ','; 
    		
        -- find position of first occurence of comma in temp string
        SET foundDelimPos = instr(tmpStr,delim); 
    		
        WHILE foundDelimPos <> 0 do
    	set element = substring(tmpStr, 1, foundDelimPos-1);
    	set tmpStr = substring(tmpStr, foundDelimPos+1); 
     				
     	INSERT INTO tbl_accreditations (accreditation_id, js_id) VALUES (element, jobseekerId);
    				
    	set foundDelimPos = instr(tmpStr,delim);
        END WHILE;
    
        -- insert last accreditation id in string
        IF tmpStr <> '' THEN
    		INSERT INTO tbl_accreditations (accreditation_id, js_id) VALUES (tmpStr, jobseekerId);
        END IF
    
    commit;		
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •