Split strings using mysql

Question

I want to create a stored procedure which will do matching of two tables. My requirement is to match two tables based on the columns user passes as an input.

Syntax:

CREATE PROCEDURE reconcile.matchTables(   IN TAB1 VARCHAR(25),    IN TAB1 VARCHAR(25),    IN COLS1 VARCHAR(250) ,    IN COLS2 VARCHAR(250)) 

EX:

matchTables('table1', 'table2', 'col1#col2#col3#col4' , 'col2#col13#col1#col8') 

Now the stored procedure should form the where clause like the following

table1.col1 = table2.col2    and table1.col2 = table2.col13    and table1.col3 = table2.col1    and table1.col4 = table2.col8 

Answer 1

MySQL does not include a function to split a delimited string. However, it’s very easy to create your own function.

User define function:

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} 

Function:

CREATE FUNCTION SPLIT_STR(   x VARCHAR(255),   delim VARCHAR(12),   pos INT ) RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),        LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),        delim, ''); 

Usage:

SELECT SPLIT_STR(string, delimiter, position)