Selon Jay Blanchard <[EMAIL PROTECTED]>: > [snip] > I've got a converted from Excel spreadsheet to mysql database, which has > mixed case column names and > also columns beginning with a digit eg 01_name, 02_address etc what > upsets PHP considerably. > > So I'd like to have a way to generically rename all columns beginning > with a numeric > form ^\d.* to someprefix_& > > Is it not possible to write such a query, or is this best done by Perl > or PHP script? > [/snip] > > It is best done using a programming language of some sort. I tried this > test to confirm; > > SET @new_prefix='foo_'; > ALTER TABLE `table` CHANGE `01_col` CONCAT(@new_prefix, `01_col`) > VARCHAR(10); > > The result, if this had worked, would have been a column named > `foo_01_col`. The query throws a syntax error at the CONCAT. This is > confirmed by substituting a string for the variable in the query > (CONCAT(foo_, 01_col)).I have not tested with a subquery as my test > platform is not running 4.1 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
Hi, now as i said, combine show tables and desc <table_name> to fetch columns array with corresponding table name, and play the query above (PERL/DBI or PHP). Mathias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
