Split strings à l'aide 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 deux tables basées sur les passes de l'utilisateur de colonnes 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 devrait former la clause where comme la suivante
table1.col1 = table2.col2
and table1.col2 = table2.col13
and table1.col3 = table2.col1
and table1.col4 = table2.col8
23
demandé sur
Rana Ghosh
2010-10-12 15:37:32
6 réponses
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)
1
répondu
Nilesh Patel
2017-04-19 11:42:09
Réponse
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
)
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
1
répondu
Rohit Kumar
2017-05-25 11:29:03
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, exécutez-le en utilisant :::
EXEC matchTables 'table1', 'table2', 'col1#col2#col3#col4' , 'col2#col13#col1#col8'
j'espère que vous avez souhaité clause where. acclamations
0
répondu
PrinceKayastha
2017-06-08 08:50:51
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, '');
0
répondu
Dimgold
2017-06-22 07:23:12
Il n'y a pas de fonction pour séparer une chaîne. Vous pouvez le faire comme ceci :
DROP PROCEDURE IF EXISTS matchTables;
DELIMITER |
CREATE PROCEDURE matchTables(
IN TAB1 VARCHAR(25),
IN TAB2 VARCHAR(25),
IN COLS1 VARCHAR(250),
IN COLS2 VARCHAR(250)
)
BEGIN
SET @col1Values = COLS1;
SET @col2Values = COLS2;
SET @whereClause = ' WHERE ';
SET @anotherCondition = '';
WHILE (LOCATE('#', @col1Values) > 0)
DO
SET @value1 = SUBSTRING(@col1Values, 1, LOCATE('#', @col1Values) - 1);
SET @value2 = SUBSTRING(@col2Values, 1, LOCATE('#', @col2Values) - 1);
SET @col1Values = SUBSTRING(@col1Values, LOCATE('#', @col1Values) + 1);
SET @col2Values = SUBSTRING(@col2Values, LOCATE('#', @col2Values) + 1);
SET @whereClause = CONCAT(@whereClause, @anotherCondition, TAB1, '.', @value1, ' = ', TAB2, '.', @value2, ' ');
SET @anotherCondition = ' AND ';
END WHILE;
SET @value1 = SUBSTRING(@col1Values, 1, LENGTH(@col1Values));
SET @value2 = SUBSTRING(@col2Values, 1, LENGTH(@col2Values));
SET @whereClause = CONCAT(@whereClause, @anotherCondition, TAB1, '.', @value1, ' = ', TAB2, '.', @value2, ' ');
SET @qry = CONCAT('SELECT * FROM ', TAB1, ', ', TAB2, @whereClause);
PREPARE stmt1 FROM @qry;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END|
DELIMITER ;
-1
répondu
Sébastien Temprado
2017-04-01 08:13:33
CHAR_LENGTH-retour longueur correcte 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, '');
-1
répondu
Azamat Galiyev
2017-10-20 23:47:48