Fractionnement des chaînes de l'utilisation de mysql

Je veux créer une procédure stockée qui va faire l'appariement de deux tables. Mon exigence est de faire correspondre les deux tables basées sur les colonnes de l'utilisateur passe comme une entrée.

Syntaxe:

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')

Maintenant la procédure stockée doit former la clause where comme suit

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

Double Possible de Pouvez-vous split/exploser un champ dans une requête MySQL?
J'ai une question stupide: depuis les entrées de l'utilisateur deux paramètres (les deux de la liste de colonnes), pourquoi ne pas vous laisser insérer 8 paramètres (un par colonne)? Est le nombre de ces colonnes variable?

InformationsquelleAutor Anil Kumar.C | 2010-10-12

MySQL n'inclut pas de fonction pour séparer une chaîne délimitée. Cependant, il est très facile de créer votre propre fonction.

Utilisateur de définir la fonction:

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

Fonction:

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, '');

Utilisation:

SELECT SPLIT_STR(string, delimiter, position)

InformationsquelleAutor Nilesh Patel

Réponse

Tableau 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
)

Tableau 2

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

Procédure Stockée

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

Hypothèses: les Types de données des colonnes mappées dans les deux tables doivent être la même.

InformationsquelleAutor Rohit Kumar

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

Maintenant, l'exécuter en utilisant :::

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

j'espère que vous avez souhaité clause where. cheers

InformationsquelleAutor PrinceKayastha

Vous pouvez construire votre propre fonction:

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, '');

InformationsquelleAutor Dimgold

-1

CHAR_LENGTH - retour bonne longueur en caractères

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, '');

InformationsquelleAutor Azamat Galiyev

Vous devez vous connecter pour publier un commentaire.