Perl Guru Forums: Perl Programming Help: Regular Expressions: Extracting Stuff from a SQL Table Dump

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: Regular Expressions:
Extracting Stuff from a SQL Table Dump

AsymptoticCoder
New User

Jan 16, 2005, 4:49 PM

Post #1 of 3 (1594 views)

Extracting Stuff from a SQL Table Dump	Can't Post

I am not sure how to approach this, but I am pretty sure I need a regular expression.

I have several hundred SQL statements like this from a table dump in a dump file:

Code

INSERT INTO `cpg132_pictures` VALUES (1, 2, 'webpics/nyc/', 'FILENAME', 18891, 20930, 233, 350, 0, '20041125144226', 1096680526, 1, '', 0, 0, '', 'DESCRIPTION', '', 'YES', '', '', '', '', 0, 2, '', '');

I want to extract FILENAME and DESCRIPTION from the statement so that I can form and output UPDATE queries in a new file, like:

Code

UPDATE cpg132_pictures SET caption='DESCRIPTION' WHERE filename='FILENAME';

How would I go about this?

Thanks,
Ryan

varakal
New User

Jan 25, 2005, 11:46 AM

Post #2 of 3 (1572 views)

Re: [AsymptoticCoder] Extracting Stuff from a SQL Table Dump [In reply to]	Can't Post

Try this

while (<>) {

($a,$filename,$b,$description) = ($_ =~ m/^INSERT.*?VALUES(.*?,.*?){3}'(.*?)'(.*?,.*?){13}'(.*?)'.*$/);

}

$filename and $description would give you the requried fields.

MrPJ
User

Feb 6, 2005, 3:39 PM

Post #3 of 3 (1449 views)

Re: [AsymptoticCoder] Extracting Stuff from a SQL Table Dump [In reply to]	Can't Post

Code

while (<>) { 
    my @wanted = (split(/[',]/, $_))[6,27]; 
}

That will work provided there are not commas or single quotes within the field values.

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives