Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: Remove column prefixes in result

    DB: MySQL 5

    Is there a way to remove a prefix from each column name in a result, with a select statement, without having to specifically mention each column name? It might seem rather pointless but I'd like to use this in a joined table view where each table uses a different prefix, allowing me to update both tables without using the prefix.
    I'd like to prevent having to specifically specify each column like 'bla_blaat AS blaat, bla_blabla AS blabla, etc.' since this can become a very long list which you'd have to update each time you change the original table(s).
    Achieving the goal from the other side on UPDATE isn't possible or preferable either. The DB probably won't allow me to be non-specific about the column names and setting the values not including a column name forces me to maintain a very accurate order of specifying to values. Dropping the prefixes would be the easiest solution but this is something I'd like to prevent.

    The following didn't work as expected: "SELECT * AS SUBSTRING(*, LOCATE('_', *) + 1) FROM user".

    It seems you can do all sorts of tricks after the SELECT but non include using the * wild card.

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    Post moved to mySQL topic.
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    there is only one way to solve your conundrum

    back up your data, drop all your tables, redefine them without the prefixes, and reload your data

    let me know when you've done that, because you may need to change some of your join queries | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts