MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:mfatene Date:May 11 2005 1:08pm
Subject:RE: Re: query to lowercase database columns names
View as plain text  
Selon Jay Blanchard <jay.blanchard@stripped>:

> [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/mysql?unsub=1
>
>

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


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