How to concat two fields in Postgresql

Question

I have 2 fields first_name and last_name but i need concatenation of these fields as fullname.

My code is below

User.where('(users.first_name ILIKE ?) OR (users.last_name ILIKE ?)', "%#{search}%", "%#{search}%")

but i need something like

User.where('(users.fullname ILIKE ?) OR (users.first_name ILIKE ?) OR (users.last_name ILIKE ?)', "%#{search}%", "%#{search}%", "%#{search}%")

fl00r · Accepted Answer · 2013-04-11T23:04:18.930

14

concatenation in Postgres like in SQLite: ||

(users.first_name || ' ' || users.last_name)

User.where('((users.first_name || ' ' || users.last_name) ILIKE ?) OR (users.first_name ILIKE ?) OR (users.last_name ILIKE ?)', "%#{search}%", "%#{search}%", "%#{search}%")

edited Apr 11 '13 at 23:04

answered Apr 17 '11 at 19:37

fl00r

82,987
33
217
237

I haven't got postgres on my laptop to test it, but I am pretty sure I am right. Checkout this: http://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query – fl00r Apr 17 '11 at 19:55
and there was a typo: I was using `LIKE` instead of `ILIKE` – fl00r Apr 17 '11 at 19:56
/app/.bundle/gems/ruby/1.8/gems/activerecord-3.0.5/lib/active_record/connection_adapters/abstract_adapter.rb:207:in `log' /app/.bundle/gems/ruby/1.8/gems/activerecord-3.0.5/lib/active_record/connection_adapters/postgresql_adapter.rb:493:in `execute' /app/.bundle/gems/ruby/1.8/gems/activerecord-3.0.5/lib/active_record/connection_adapters/postgresql_adapter.rb:983:in `select_raw' /app/.bundle/gems/ruby/1.8/gems/activerecord-3.0.5/lib/active_record/connection_adapters/postgresql_adapter.rb:976:in `select' – mike101 Apr 17 '11 at 20:08
new useful link for you http://stackoverflow.com/questions/2986405/database-independant-sql-string-concatenation-in-rails – fl00r Apr 17 '11 at 20:10
5

You need single quotes instead of double quotes. – Peter Eisentraut Apr 18 '11 at 06:14

score 5 · Answer 2 · edited Aug 02 '12 at 19:37

5

Change it to use single quotes and it will work:

(users.first_name || ' ' || users.last_name)

Instead of:

(users.first_name || " " || users.last_name)

edited Aug 02 '12 at 19:37

DisplayName

3,093
5
35
42

answered Aug 02 '12 at 18:13

Nate

51
1
1

How to concat two fields in Postgresql

2 Answers2