Table Splitting by semi-colon 1

[proximity]

Hi,

Does anyone have any ideas how I get tableA to look like tableB? Ignoring the fact that the design of tableA is way short of ideal, it's what I have to work around, so any help will be greatly appreciated:

tableA
id item stamp
1 111111;111112;111113;111114 10/03/2011 12:00:35
2 111115;111116 10/03/2011 12:10:00
3 222333;222444;222555 10/03/2011 12:20:00
4 851112;321321;321258;232132 10/03/2011 12:30:00

tableB
id item stamp
1 111111 10/03/2011 12:00:35
2 111112 10/03/2011 12:00:35
3 111113 10/03/2011 12:00:35
4 111114 10/03/2011 12:00:35
5 111115 10/03/2011 12:10:00
6 111116 10/03/2011 12:10:00
7 222333 10/03/2011 12:20:00
8 222444 10/03/2011 12:20:00
9 222555 10/03/2011 12:20:00
10 851112 10/03/2011 12:30:00
11 321321 10/03/2011 12:30:00
12 321258 10/03/2011 12:30:00
13 232132 10/03/2011 12:30:00

Thanks,

--
Steven

 

[r937]

do you really need a new auto_increment? if so, you will need to CREATE TABLE tableB first

then use SUBSTRING_INDEX as follows...

INSERT
  INTO tableB
     ( item
     , stamp )
SELECT SUBSTRING_INDEX(item,';',1)
     , stamp
  FROM tableA;

INSERT
  INTO tableB
     ( item
     , stamp )
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(item,';',2),';',-1)
     , stamp
  FROM tableA;

INSERT
  INTO tableB
     ( item
     , stamp )
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(item,';',3),';',-1)
     , stamp
  FROM tableA;

INSERT
  INTO tableB
     ( item
     , stamp )
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(item,';',4),';',-1)
     , stamp
  FROM tableA;

if you don't need the auto_increment, then simply run all the SELECTs without the INSERTs, and UNION them together

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[proximity]

Thanks r937!

That works a treat except for one thing . . . . it will return 16 records instead of 13 (the additional 3 records are dulpicates of existing ones). I can elimate those with an 'insert into select distinct' query . . . . but I would be interested to know if there is another way to avoid having to do this?

Once again, huge thanks for your help

 

[r937]

the better way is not to put DISTINCT into the SELECT query, but to put UNIQUE onto the table column you're inserting into, and then run INSERT IGNORE

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[proximity]

I never thought of that!!!! Much more elegant solution. Thanks again for your time and your help,
--
Steven