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

Possible duplicate of Can you split/explode a field in a MySQL query? — Mark Amery
– Mark Amery, Commented Apr 1, 2017 at 12:25
I have a stupid question: since the user inputs two parameters (the two list of columns), why don't you let her insert 8 parameters (one per column)? Is the number of those columns variable? — Erenor Paz
– Erenor Paz, Commented May 2, 2017 at 15:20

wkl · Accepted Answer · 2017-04-19 11:42:09Z

3

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)

edited Apr 19, 2017 at 11:42

wkl

1,8942 gold badges17 silver badges28 bronze badges

answered Apr 19, 2017 at 10:32

Nilesh Patel

5875 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rohit Kumar · Accepted Answer · 2017-05-25 11:29:03Z

Answer

Table 1

CREATE TABLE `Table1` (
  `Col1` varchar(100) DEFAULT NULL,
  `Col2` varchar(100) DEFAULT NULL,
  `Col3` varchar(100) DEFAULT NULL,
  `Col4` varchar(100) DEFAULT NULL,
  `DummyColumn` varchar(45) DEFAULT NULL
)

Table 2

CREATE TABLE `Table2` (
  `col2` varchar(100) DEFAULT NULL,
  `col13` varchar(100) DEFAULT NULL,
  `col1` varchar(100) DEFAULT NULL,
  `col8` varchar(100) DEFAULT NULL
)

Stored Procedure

CREATE DEFINER=`Connect7827`@`%` PROCEDURE `reconcile.matchTables`(
 IN TAB1 VARCHAR(25), 
  IN TAB2 VARCHAR(25), 
  IN COLS1 VARCHAR(250) , 
  IN COLS2 VARCHAR(250))
StartfromHere: BEGIN


        DECLARE NoOfColumnInTable1  INT unsigned DEFAULT 0;   
        DECLARE NoOfColumnInTable2 INT unsigned DEFAULT 0;
        Declare Column1Count int default 1;
        Declare Column2Count int default 1;
        Declare vPickOneValue varchar(100);
        Declare querystring varchar(8000);
        Declare NoOFRowsInFinalResult int default 1;
        Declare _Tab1 varchar(1000);
        Declare _TAB2 varchar(1000);
        Declare _COLS1 varchar(1000);
        Declare _COLS2 varchar(1000);


        -- Column Names for Table 1 
        DROP TEMPORARY TABLE IF EXISTS Table1_Columns;
        CREATE TEMPORARY TABLE Table1_Columns(Column_Name varchar(100));
        SET @buffer= CONCAT_WS('','insert into Table1_Columns(Column_Name)
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = ',"'",TAB1,"'");
        -- Select @buffer;
        PREPARE stmt FROM @buffer;
        EXECUTE stmt;
        -- Column Names for Table 2 
        DROP TEMPORARY TABLE IF EXISTS Table2_Columns;
        CREATE TEMPORARY TABLE Table2_Columns(Column_Name varchar(100));
        SET @buffer= CONCAT_WS('','insert into Table2_Columns(Column_Name)
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = ',"'",TAB2,"'");
        -- Select @buffer;
        PREPARE stmt FROM @buffer;
        EXECUTE stmt;

        Set NoOfColumnInTable1=(Select count(*) from Table1_Columns);
        Set NoOfColumnInTable2=(Select count(*) from Table2_Columns);

        --    Select NoOfColumnInTable1,NoOfColumnInTable2;
        if (NoOfColumnInTable1=0) then
            Select 'Table 1 not found in database'as'Result';
            leave StartfromHere;
        end if;
        if (NoOfColumnInTable2=0) then
            Select 'Table 2 not found in database' as'Result' ;
            leave StartfromHere;
        end if;
        IF (NoOfColumnInTable1!=NoOfColumnInTable2) then
            Select 'No of column to be joined must be equal.'as'Result';
            leave StartfromHere;
        end if; 

        DROP TEMPORARY TABLE IF EXISTS BasedOn_Col1_List;
        CREATE TEMPORARY TABLE BasedOn_Col1_List(ID int NOT NULL AUTO_INCREMENT, Column_Name varchar(100), PRIMARY KEY (id));
        while Column1Count< NoOfColumnInTable1+1 do
               set @Query=CONCAT_WS('' ,"insert into BasedOn_Col1_List(Column_Name) Select SUBSTRING_Index('",COLS1,"','#',",Column1Count,");");
               -- Select @Query as'Value';
                PREPARE stmt1 FROM @Query;
                EXECUTE stmt1;
                SET Column1Count=Column1Count+1;
        end while;
        SET Column1Count=1;
        WHILE Column1Count<=NoOfColumnInTable1 do 
            SET vPickOneValue=(Select Concat(Column_Name,"#") from BasedOn_Col1_List where ID=Column1Count);
            update BasedOn_Col1_List set Column_Name=replace(Column_Name,vPickOneValue,"") where ID<>Column1Count;
            -- Select  vPickOneValue;
            SET Column1Count=Column1Count+1 ;
        end while;

        --   Preapre Table from Column2 Parameter
        DROP TEMPORARY TABLE IF EXISTS BasedOn_Col2_List;
        CREATE TEMPORARY TABLE BasedOn_Col2_List(ID int NOT NULL AUTO_INCREMENT, Column_Name varchar(100), PRIMARY KEY (id));
        while Column2Count< NoOfColumnInTable2+1 do
               set @Query=CONCAT_WS('' ,"insert into BasedOn_Col2_List(Column_Name) Select SUBSTRING_Index('",COLS2,"','#',",Column2Count,");");
               -- Select @Query as'Value';
                PREPARE stmt2 FROM @Query;
                EXECUTE stmt2;
                SET Column2Count=Column2Count+1;
        end while;
        SET Column2Count=1;
        WHILE Column2Count<=NoOfColumnInTable2 do 
            SET vPickOneValue=(Select Concat(Column_Name,"#") from BasedOn_Col2_List where ID=Column2Count);
            update BasedOn_Col2_List set Column_Name=replace(Column_Name,vPickOneValue,"") where ID<>Column2Count;
            -- Select  vPickOneValue;
            SET Column2Count=Column2Count+1 ;
        end while;

        DROP TEMPORARY TABLE IF EXISTS TableFromColumnList;
        CREATE TEMPORARY TABLE TableFromColumnList
                    (    ID int NOT NULL AUTO_INCREMENT, 
                        Table1Name varchar(100),
                        Column_Name_Table1 varchar(100), 
                        Table2Name varchar(1000), 
                        Column_Name_Table2 varchar(100), 
                        PRIMARY KEY (id)
                    );
        Insert into TableFromColumnList(Column_Name_Table1,Column_Name_Table2,Table1Name,Table2Name)    
        select    t1.Column_Name,t2.Column_Name,TAB1,TAB2
        from BasedOn_Col1_List t1 , BasedOn_Col2_List t2  where t1.Id=t2.id;        


        -- -- Preparing the final Result ----------------
        While NoOFRowsInFinalResult<=NoOfColumnInTable2 do -- / Or NoOFRowsInFinalResult<=NoOfColumnInTable2 --

            SET _Tab1  =(Select Table1Name from TableFromColumnList where Id=NoOFRowsInFinalResult);
            SET _COLS1  =(Select Column_Name_Table1 from TableFromColumnList where Id=NoOFRowsInFinalResult);
            SET _TAB2 =(Select Table2Name from TableFromColumnList where Id=NoOFRowsInFinalResult);
            SET _COLS2 =(Select Column_Name_Table2 from TableFromColumnList where Id=NoOFRowsInFinalResult);

            IF NoOFRowsInFinalResult=1 then
                SET querystring =concat_ws("" , querystring,_Tab1,".", _COLS1 , "=",_Tab2,".", _COLS2,"   ");
            else
                SET querystring =concat_ws("" , querystring ,"and",_Tab1,".", _COLS1 , "=" ,_Tab2,".", _COLS2 ,"   ");
            end if;

            SET NoOFRowsInFinalResult=NoOFRowsInFinalResult+1 ;
        End while;
        SET querystring=concat_ws("","Select * from ",TAB1,", "  ,TAB2," where ",querystring);
        Select querystring;

END

Assumptions: DataTypes of columns to be mapped in both the tables must be same.

Neo · Accepted Answer · 2017-06-08 08:50:51Z

CREATE PROCEDURE matchTables
@TAB1 VARCHAR(25), 
@TAB2 VARCHAR(25), 
@COLS1 VARCHAR(250) , 
@COLS2 VARCHAR(250)

AS


BEGIN
DECLARE @WHEREstring VARCHAR(MAX)
SET @WHEREstring = 
'   
WHERE
        ' 
SELECT @WHEREstring = @WHEREstring + @TAB1 +'.'+ tab1.col+' = '+@TAB2+'.' + tab2.col +' AND
        ' 
            FROM
            (
                SELECT QUOTENAME(split.a.value('.','VARCHAR(100)')) AS col, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum
                    FROM 
                    (
                        SELECT Cast ('<M>' + Replace(@COLS1, '#', '</M><M>')+ '</M>' AS XML) AS Tab1Data
                    ) AS A 
                    CROSS apply Tab1Data.nodes ('/M') AS Split(a)
            ) tab1
            INNER JOIN
            (
                Select QUOTENAME(AliasSplit.c.value('.', 'varchar(100)')) AS col, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum
                FROM 
                (
                    SELECT Cast ('<A>' + Replace(@COLS2, '#', '</A><A>')+ '</A>' AS XML) AS Tab2Data
                ) AS A 
                CROSS apply Tab2Data.nodes ('/A') AS AliasSplit(c)
            ) tab2
            ON tab1.RowNum = tab2.RowNum
        SET @WHEREstring= LEFT(@WHEREstring, LEN(@WHEREstring) - 8)

        print @WHEREstring
END

Now, execute it using :::

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

i hope you got the desired where clause. cheers

Dimgold · Accepted Answer · 2017-06-22 07:23:12Z

0

You can build your own function:

CREATE FUNCTION String_split(inp VARCHAR(255),del VARCHAR(255),loc INT)
RETURNS VARCHAR(255)
RETURN REPLACE(Substring(Substring_index(inp, del,loc),LENGTH(Substring_index(inp, del, loc-1)) + 1),del, '');

answered Jun 22, 2017 at 7:23

Dimgold

2,9545 gold badges30 silver badges50 bronze badges

Comments

SE1986 · Accepted Answer · 2017-10-20 23:47:48Z

-1

CHAR_LENGTH - return correct length in chars

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

edited Oct 20, 2017 at 23:47

SE1986

2,7901 gold badge15 silver badges34 bronze badges

answered Oct 20, 2017 at 23:32

Azamat Galiyev

992 bronze badges

Collectives™ on Stack Overflow

Split strings using mysql

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related