Hi,
SenTnel wrote:
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
Ah, denormalized data that you want to normalize :-) If you are going to be
using non-SQL code to do any part of this process, it might be easiest to do it
there. In Perl, for example,
foreach my $row ( $select->fetchrow_arrayref ) {
foreach my $room ( split('-', $row->[1]) ) {
$insert->execute( .... )
}
}
If you must do this in SQL, most solutions will have you writing a stored
procedure that does a lot of looping over character positions and pulling out
substrings. But there's a better way, using the numbers table and a single
SELECT. There are good explanations here:
http://www.sommarskog.se/arrays-in-sql.html Especially look at
http://www.sommarskog.se/arrays-in-sql-2000.html#tblnum-core These articles are
written for SQL Server, but the same thing can be done on MySQL. SQL For
Smarties also covers this technique.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
