Do you think it's possible to patch a query using sql-formatter parser?

[sandvige]

I have to remove aliases from an SQL Query (to be honest, it's not SQL, it's CQL, Cassandra Query Language).

Here is a query:

• SELECT a.field AS fieldAlias, a.otherField AS otherFIeldAlias FROM table a WHERE a.field = ?

I'd like to transform it into:

• SELECT field AS fieldAlias, otherField AS otherFIeldAlias FROM table WHERE field = ?

Do you think sql-formatter can handle this?

[stof]

I don't think this is a hob for the SqlFormatter library

[sandvige]

Do you have any lib to point out?

[jdorn]

I think this functionality is too specific to add to the library, but you might be able to make it work by editing the source and adding your own method. I don't know of any other library that could do this, so it might be worth a shot to try.

If you're trying to remove the alias a, you basically need to remove the following from the string:

• as a
• as a
• a.
• a.
• a
• a

This isn't going to work for every query, but it should for the majority of them.

Here's some code to get started. It's off the top of my head, so there may be typos:

class SqlFormatter {
//...
  public function removeAlias($sql, $alias) {
    // Tokenize the input sql
    $tokens = self::getTokens($sql);

    $new_sql = '';

    // Loop through the tokens and remove aliases
    for($i=0; $i<count($tokens); $i++) {
      $token = $tokens[$i];

      // "as a"
      if(strtolower($token[self::TOKEN_VALUE]) === "as") {
        if ($tokens[$i+1][self::TOKEN_TYPE] === self::TOKEN_TYPE_WHITESPACE && $tokens[$i+2][self::TOKEN_VALUE] === $alias) {
          $i+=2;
          continue;
        }
      }

      // TODO: "as `a`", "a.", "`a`.", "a", and "`a`"

      $new_sql .= $token[self::TOKEN_VALUE];
    }

    return $new_sql;
  }
//...
}