remove text from string

shantanuo · #1 (**permalink**) 11-06-2008

I use the following command to know the create table structure.

mysqldump myDBName myTBName -d --compact

And I get the following output...

CREATE TABLE `tblThreads` (
`threadID` smallint(4) unsigned zerofill NOT NULL auto_increment,
`threadTitle` varchar(255) NOT NULL default '',
PRIMARY KEY (`threadID`)
) ENGINE=MyISAM AUTO_INCREMENT=154 DEFAULT CHARSET=latin1 COMMENT='Forum threads';

I want to remove everything after AUTO_INCREMENT till the end of that line. But I want to retain the last semicolon ;
The engine can be MyISAM or InnoDB or Memory and so on. something like...

mysqldump myDBName myTBName -d --compact | grep --keep engine --remove others

zaxxon · #2 (**permalink**) 11-06-2008

Something like this?

Code:

root@isau02:/data/tmp/testfeld> cat infile
CREATE TABLE `tblThreads` (
`threadID` smallint(4) unsigned zerofill NOT NULL auto_increment,
`threadTitle` varchar(255) NOT NULL default '',
PRIMARY KEY (`threadID`)
) ENGINE=MyISAM AUTO_INCREMENT=154 DEFAULT CHARSET=latin1 COMMENT='Forum threads';
root@isau02:/data/tmp/testfeld> sed 's/\(^.*AUTO_INCREMENT[^ ]*\) .*/\1;/g' infile
CREATE TABLE `tblThreads` (
`threadID` smallint(4) unsigned zerofill NOT NULL auto_increment,
`threadTitle` varchar(255) NOT NULL default '',
PRIMARY KEY (`threadID`)
) ENGINE=MyISAM AUTO_INCREMENT=154;

shantanuo · #3 (**permalink**) 11-06-2008

I do not want auto_increment as well, but do want engine...
ENGINE=MyISAM ;

zaxxon · #4 (**permalink**) 11-06-2008

Code:

sed 's/\(.*ENGINE=MyISAM[^ ]*\) .*/\1;/g' infile

shantanuo · #5 (**permalink**) 11-06-2008

Thanks.
Only 2 points to note...
1) The engine can be something else like InnoDB and not necessarily MyISAM
2) Though it was not mentioned in the original question, is it possible to keep auto_increment but reset it to 0?
ENGINE=SomeThingChanging AUTO_INCREMENT=0;

zaxxon · #6 (**permalink**) 11-06-2008

Code:

sed 's/\(.*ENGINE.*AUTO_INCREMENT=\).*/\10;/g' infile

shantanuo · #7 (**permalink**) 11-07-2008

Perfect and amazing.
(I guess, mysqldump does not have any built-in tool to do this)

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Best way to remove sections of text from a file	cals64	Shell Programming and Scripting	8	09-22-2008 01:16 PM
Search a string and append text after the string	kesu2k	Shell Programming and Scripting	8	07-18-2008 08:35 PM
remove specified text from file	raidzero	Shell Programming and Scripting	2	06-19-2008 10:10 AM
Remove duplicate text	dejavu88	Shell Programming and Scripting	7	06-08-2008 05:37 PM
appending string to text file based on search string	malaymaru	Shell Programming and Scripting	1	06-09-2006 09:53 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread: