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