MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Jay Blanchard Date:May 11 2005 12:10pm
Subject:RE: Re: query to lowercase database columns names
View as plain text  
[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
Thread
query to lowercase database columns nameszzapper9 May
  • Re: query to lowercase database columns nameszzapper11 May
    • Re: query to lowercase database columns namesmfatene11 May
  • Re: query to lowercase database columns nameszzapper11 May
RE: Re: query to lowercase database columns namesJay Blanchard11 May
  • RE: Re: query to lowercase database columns namesmfatene11 May
  • Re: query to lowercase database columns nameszzapper12 May