Blob field: delimiting text, exporting

[SenTnel]

Hi all!

I have a table that contains a column (blob, text type, formatted as MEDIUMTEXT) with text hyphen (-) delimited, listing

rooms by numbers, and I need to export or insert that data into another table, where all other info on the other columns

remains the same, but I need the data on the blob field to be single listed

another words, i have a table like this (example):

School Rooms
Your Hometown High School 1034-1035-1037-1039
My Hometown High School 208-178-1432-1728

I need it like this:

School Rooms
Your Hometown High School 1034
Your Hometown High School 1035
Your Hometown High School 1037
Your Hometown High School 1039
My Hometown High School 208
My Hometown High School 178
My Hometown High School 1432
My Hometown High School 1728

Suggestions? Thanks!

 

[hvass]

Prior to stored proceedures and functions you had to do something like:

set @rooms="876-98-97-99";
select
	substring_index(@rooms,"-",1),
	trim(leading "-" from substring_index(substring_index(@rooms,"-",2),substring_index(@rooms,"-",1),-1)),
	trim(leading "-" from substring_index(substring_index(@rooms,"-",3),substring_index(@rooms,"-",2),-1)),
	trim(leading "-" from substring_index(substring_index(@rooms,"-",4),substring_index(@rooms,"-",3),-1))
	;

Now you could try one of the split style functions available on mysqlforge for example

http://forge.mysql.com/snippets/view.php?id=58

But if you only have a reasonable maximum number of rooms for each school the old fashioned way is easy enough

set @rooms="876-98-97-99";
create table sometable select  substring_index(@rooms,"-",1);
insert into sometable select trim(leading "-" from substring_index(substring_index(@rooms,"-",2),substring_index(@rooms,"-",1),-1));
insert into sometable select trim(leading "-" from substring_index(substring_index(@rooms,"-",3),substring_index(@rooms,"-",2),-1));
insert into sometable select trim(leading "-" from substring_index(substring_index(@rooms,"-",4),substring_index(@rooms,"-",3),-1));

 

[SenTnel]

Thanks!

 

[r937]

what i tole ya -- SUBSTRING_INDEX, and it gets messy ;-)

you will need to modify the above query if you have "not always four numbers, could be 3, 4, 5 numbers"

r937.com | rudy.ca

 

[Kozusnik]

I think this would be simpler using a programming language outside of the sql, but I'm not sure if that's an option.
Mark

 

[hvass]

Yes simpler to use a programming language.

Does anyone know of a UDF written to do this? My C experience is so poor I am really only at B & a half so a UDF would be a real struggle.

I heard/read somewhere that MySQL were considering using a version of PHP as an embedded language extension. Would be great to get all the PHP functions available as if it were a mod_php style thing.