To convert a string into table with schema

Question

You are provided with a string having delimiters of two types. The schema of the table is provided. How to convert the delimited string into a temporary table in an efficient manner? For example,

Andy~22~US|Jane~24~Australia|Davis~30~UK|Nancy~32~Germany

I am new to MySql. So any help will be greatly appreciated?

any option to use some server side script (like PHP) or you insist SQL only? — mitkosoft, Apr 22 '16 at 12:30
In MySql only. I know how to do the same thing in c# and php. Anyway thanks. Appreciate your concern. — Shikhar Maheshwari, Apr 22 '16 at 12:33
Did you check this related Thread: http://stackoverflow.com/questions/18304857/split-delimited-string-value-into-rows? — Techie, Apr 22 '16 at 12:42

Mārcis Liepiņš · Accepted Answer · 2016-05-04T20:39:03.957

0

I would write it like this maybe there is sufficient way with Name , but it has to work!

Create table MIX (
`id` int not null,
`x` text not null)
ENGINE = InnoDB;
insert into mix value (1,'Andy~22~US|Jane~24~Australia|Davis~30~UK|Nancy~32~Germany');
create table unmix select
numbers.n as id,
SUBSTRING_INDEX(SUBSTRING_INDEX(x, '~', 2*numbers.n),'~', -1) as Age,
SUBSTRING_INDEX(SUBSTRING_INDEX(x, '|', 1*numbers.n),'~', -1) as Country,
substring_index(SUBSTRING_INDEX(SUBSTRING_INDEX(x,'~', 2*numbers.n),'|',-1),'~',+1) as Name
from
(select 1 n union all
 select 2 union all 
 select 3 union all select 4 ) 
 numbers INNER JOIN mix
on CHAR_LENGTH(mix.x)
 -CHAR_LENGTH(REPLACE(mix.x, ' ', ''))>=numbers.n-4;

edited May 04 '16 at 20:39

answered Apr 22 '16 at 14:19

Mārcis Liepiņš

76
14

The result set that I am getting executing the above query in mysql workbench is not as expected. There are only three records coming when the actual records must be 4, – Shikhar Maheshwari Apr 30 '16 at 11:47
@shikharMaheshwari fixed it! – Mārcis Liepiņš May 02 '16 at 12:20
your answered code does not work! whit what i should replace cwuser? – Mārcis Liepiņš May 02 '16 at 12:29
It was the user name of my database user. You can replace it with any database user having rights to create SP. – Shikhar Maheshwari May 02 '16 at 17:36
@Marcis, your query gave the desired result. Only, the id column was having the same value 1 across. Please fix it. Oherwise, I accept your answer. Thanks for your co-operation. – Shikhar Maheshwari May 03 '16 at 09:01
@ShikharMaheshwari I fixed it! – Mārcis Liepiņš May 04 '16 at 20:39
Thanks for fixing and updating. – Shikhar Maheshwari May 05 '16 at 04:41

score 0 · Answer 2 · answered Apr 25 '16 at 16:48

Thankfully I wrote a simple code for myself:

DROP procedure IF EXISTS `split_procedure`;

DELIMITER $$

CREATE DEFINER=`cwuser`@`%%` PROCEDURE `split_procedure`(v_stringtosplit text, v_rowdelim CHAR(5), v_coldelim CHAR(5))
BEGIN

SET @replacetosplit := REPLACE(v_stringtosplit, v_rowdelim, '"),("');
SET @replacetosplit := REPLACE(@replacetosplit, v_coldelim, '","');

SET v_stringtosplit = CONCAT(v_rowdelim, v_stringtosplit);

SET @no_of_rows = length(v_stringtosplit) - length(replace(v_stringtosplit, v_rowdelim, ''));
SET @rownum = 1;
SET @no_of_cols = length(v_stringtosplit) - length(replace(v_stringtosplit, v_coldelim, ''));
SET @no_of_cols = (@no_of_cols / @no_of_rows);
SET @colnum = 1;

DROP TABLE IF EXISTS temporary_split_table;

SET @strtabcol = 'CREATE TEMPORARY TABLE temporary_split_table (';
SET @valcol = '';

WHILE(@colnum <= @no_of_cols + 1)
DO
    SET @strtabcol = CONCAT(@strtabcol, 'COL', @colnum, ' VARCHAR(1000)', ',');
    SET @colnum = @colnum + 1;
END WHILE;

SET @strtabcol = CONCAT(LEFT(@strtabcol, LENGTH(@strtabcol)-1), ')');

PREPARE stmt FROM @strtabcol;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @strcol = 'INSERT INTO temporary_split_table VALUES("';

SET @strcol = CONCAT(@strcol, @replacetosplit, '")');
PREPARE stmt FROM @strcol;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT * FROM temporary_split_table;

END$$

DELIMITER ;

Please do not forget to delete the temporary table temporary_split_table after the use of above SP.

The method to call above SP is:

call  split_procedure('Andy~22~US|Jane~24~Australia|Davis~30~UK|Nancy~32~Germany',  '|', '~');

To convert a string into table with schema

2 Answers2

Linked