Thread: Concatenation in selects

I know it can be done, but I cannot seem to figure out how to output to
fields concatenated together in a select.  For instance, I have a table
with first_name and last_name as fields (among others).  I would like
display first_name and last_name as one field.  I want to do something
like:

   name=first_name + " " + last_name

but of course within an SQL select statement.  Thanks...james

> I know it can be done, but I cannot seem to figure out how to output to
> fields concatenated together in a select.  For instance, I have a table
> with first_name and last_name as fields (among others).  I would like
> display first_name and last_name as one field.  I want to do something
> like:
>
>    name=first_name + " " + last_name
>
> but of course within an SQL select statement.  Thanks...james

Sorry, I finally found it in the hackers mailing list.  The answer is:

  select textcat(textcat(first_name, ' '),last_name) from users;

I just couldn't seem to find that documented, I am sure it is though.
Thanks...james

>
>I know it can be done, but I cannot seem to figure out how to output to
>fields concatenated together in a select.  For instance, I have a table
>with first_name and last_name as fields (among others).  I would like
>display first_name and last_name as one field.  I want to do something
>like:
>
>   name=first_name + " " + last_name
>
>but of course within an SQL select statement.  Thanks...james
>
>
>
>
>

Hi,

Try

 select first_name || (' ' || last_name) as full_name from ...

if you have problems converting types to the ones expected by
the operator "||", try this one

 select ltrim(first_name) || (' ' || ltrim(last_name)) as full_name from
...

where "ltrim" is used only to convert both, first_time and
last_time, to text. I wish I could convert among string types
(bpchar, varchar and text) without any tricks, but it seems this
is not implemented yet.


Fidelis Assis
MCI-Embratel - Rio de Janeiro

PS.:
1- The psql command "\do" will show the list of built-in operators
   and "\df" will show the functions;

2- I use 6.3.2 with all patches applied and the OS is AIX 3.2.5

According to James Olin Oden:
>
> > I know it can be done, but I cannot seem to figure out how to output to
> > fields concatenated together in a select.  For instance, I have a table
> > with first_name and last_name as fields (among others).  I would like
> > display first_name and last_name as one field.  I want to do something
> > like:
> >
> >    name=first_name + " " + last_name
> >
> > but of course within an SQL select statement.  Thanks...james
>
> Sorry, I finally found it in the hackers mailing list.  The answer is:
>
>   select textcat(textcat(first_name, ' '),last_name) from users;
>
> I just couldn't seem to find that documented, I am sure it is though.
> Thanks...james

You should also be able to use the SQL concatenation operator '||'.  It
doesn't seem to be able to understand more than 2 things at once so
you have to use parens for grouping.  I don't know if that is an SQL
requirement or a postgresql limitation.

  select (first_name || ' ') || last_name as full_name from users;


  Les Mikesell
   les@mcs.com

> You should also be able to use the SQL concatenation operator '||'.  It
> doesn't seem to be able to understand more than 2 things at once so
> you have to use parens for grouping.  I don't know if that is an SQL
> requirement or a postgresql limitation.
>
>   select (first_name || ' ') || last_name as full_name from users;

Yes, that is in the 6.4 bugs list, and if it is not fixed, will be added
to the TODO list.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)