List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 12 2001 6:07pm
Subject:Re: varchar to char in table with several varchar's
View as plain text  
At 9:32 AM +0100 12/12/01, Willem Bison wrote:
>I have a table with several columns of type 'varchar'. How can I change all
>columns to fixed width char's ? Doing a 'alter' from varchar to char has no
>effect since the column is changed back to varchar.

You have to change them all at ones in the same ALTER TABLE statement.

Here's a Perl DBI function that takes a database handle and a table
name, and returns the ALTER TABLE statement to do what you want.
(You have to execute the statement yourself; the return value is undef
if the table contains no applicable columns.)

sub alter_to_char
{
my ($dbh, $tbl_name) = @_;
my ($sth, $str);

     $sth = $dbh->prepare ("SHOW COLUMNS FROM $tbl_name");
     $sth->execute ();
     while (my @row = $sth->fetchrow_array ())
     {
         if ($row[1] =~ /^varchar/)      # it's a VARCHAR column
         {
             $row[1] =~ s/^var//;
             $str .= ",\n\t" if $str;
             $str .= "MODIFY $row[0] $row[1]";
             $str .= ($row[2] eq "YES" ? "" : " NOT") . " NULL";
             $str .= " DEFAULT " . $dbh->quote ($row[4]);
         }
     }
    
     $sth->finish ();
     $str = "ALTER TABLE $tbl_name\n\t$str" if $str;
     return ($str);
}


Or, if you wanna go in the other direction:

sub alter_to_varchar
{
my ($dbh, $tbl_name) = @_;
my ($sth, $str);

     $sth = $dbh->prepare ("SHOW COLUMNS FROM $tbl_name");
     $sth->execute ();
     while (my @row = $sth->fetchrow_array ())
     {
         if ($row[1] =~ /^char/)     # it's a CHAR column
         {
             $row[1] = "var" . $row[1];
             $str .= ",\n\t" if $str;
             $str .= "MODIFY $row[0] $row[1]";
             $str .= ($row[2] eq "YES" ? "" : " NOT") . " NULL";
             $str .= " DEFAULT " . $dbh->quote ($row[4]);
         }
     }
    
     $sth->finish ();
     $str = "ALTER TABLE $tbl_name\n\t$str" if $str;
     return ($str);
}
Thread
varchar to char in table with several varchar'sWillem Bison12 Dec
  • Re: varchar to char in table with several varchar'sHarald Fuchs12 Dec
  • Re: varchar to char in table with several varchar'sPaul DuBois12 Dec
  • POSTGRESQL VS MYSQLRandy Johnson13 Dec