Database-independant SQL String Concatenation in Rails

Question

I want to do a database-side string concatenation in a Rails query, and do it in database-independent way.

SQL-92 specifies double-bar (||) as the concatenation operator. Unfortunately it looks like MS SQL Server doesn't support it; it uses + instead.

I'm guessing that Rails' SQL grammar abstraction has solved the db-specific operator problem already. If it does exist, how do I use it?

Answer 1

If you want something Rails neutral, you're going to need to return the values you want concatenated and do that once the data has been delivered to rails (or do it in rails before you give it to the database).

It looks like Mysql uses CONCAT(), Postgres ||, Oracle CONCAT() or ||, T-SQL +.

Any rails abstraction of the same would have to take place at a point where you could just be doing concatenation using regular Ruby, or I've completely misunderstood the question.

Answer 2

I had the same problem and never came up with anything that was built into Rails. So I wrote this little method.

# Symbols should be used for field names, everything else will be quoted as a string
def db_concat(*args)

  adapter = configurations[RAILS_ENV]['adapter'].to_sym
  args.map!{ |arg| arg.class==Symbol ? arg.to_s : "'#{arg}'" }

  case adapter
    when :mysql
      "CONCAT(#{args.join(',')})"
    when :sqlserver
      args.join('+')
    else
      args.join('||')
  end

end

I'm thinking somebody should really write some sort of SQL helper plugin that could automatically format simple SQL expressions based using the correct functions or operators for the current adapter. Maybe I'll write one myself.