Board index » delphi » Concatenating two fields in an sql statement

Concatenating two fields in an sql statement

I was wondering if any of the SQL gurus out there can help.

I want to create a view over a table which will concatinate two fields into
one. eg....

Combine field GivenName & field Surname to create a field Name.

Can this be done???

Any help would be appreciated.

Thanks in advance

Matthew Sutton
National Systems Development Manager
VIPS International Pty Ltd

 

Re:Concatenating two fields in an sql statement


Here is an example SQL

select first_name ||' ' || last_name Fullname
from employee

Use the || to concatenate. In this example I also add a blank character ( ' ' )
between first and last name. After the concatenation you can optionally
give the "new" field a name ( Fullname in this example).

Good Luck

--Kjell-?ke Boberg
ConNova Systems AB
Motala Sweden

Quote
Matthew Sutton wrote:
> I was wondering if any of the SQL gurus out there can help.

> I want to create a view over a table which will concatinate two fields into
> one. eg....

> Combine field GivenName & field Surname to create a field Name.

> Can this be done???

> Any help would be appreciated.

> Thanks in advance

> Matthew Sutton
> National Systems Development Manager
> VIPS International Pty Ltd

Re:Concatenating two fields in an sql statement


One thing should be mentioned when concatenating two fields.

If first_name or last_name is null, than the whole concatenation is null!

Regards
Thomas Steinmaurer
IB LogManager - The Logging Tool for Interbase
http://www.equitania.de/interbase/iblogmanager/start.htm (German)
http://www.equitania.de/interbase/iblogmanager/default.htm (English)
E-Mail:     mailto:iblogmana...@aon.at

"Kjell-?ke Boberg" <k...@connova.se> schrieb im Newsbeitrag
news:3AA87577.877BD21B@connova.se...

Quote
> Here is an example SQL

> select first_name ||' ' || last_name Fullname
> from employee

> Use the || to concatenate. In this example I also add a blank character
( ' ' )
> between first and last name. After the concatenation you can optionally
> give the "new" field a name ( Fullname in this example).

> Good Luck

> --Kjell-?ke Boberg
> ConNova Systems AB
> Motala Sweden

> Matthew Sutton wrote:

> > I was wondering if any of the SQL gurus out there can help.

> > I want to create a view over a table which will concatinate two fields
into
> > one. eg....

> > Combine field GivenName & field Surname to create a field Name.

> > Can this be done???

> > Any help would be appreciated.

> > Thanks in advance

> > Matthew Sutton
> > National Systems Development Manager
> > VIPS International Pty Ltd

Re:Concatenating two fields in an sql statement


Hi Thomas,

something like the DECODE function under Oracle would solve your problem. As far
as i know, such a function currently does not exist under Interbase. In my
stored procedures and triggers i replace a NULL value with an emtpy string with
a statement like:

if (TEXT is null)
then begin
  TEXT = '';
end

There is a second problem with NULL values in a comparison i had within triggers
and stored procedures. The following comparison even runs into the else branch
with only one (!) variable NULL.

if (TEXT1 <> TEXT2)
then begin
end
else begin
end

So i do something like:

if ((TEXT1 <> TEXT2) OR
    ((TEXT1 is null) and (TEXT2 is not null)) OR
    ((TEXT1 is not null) and (TEXT2 is null))
   )
then begin
end
else begin
end

Michael Gast

Quote
Thomas Steinmaurer wrote:
> One thing should be mentioned when concatenating two fields.

> If first_name or last_name is null, than the whole concatenation is null!

> Regards
> Thomas Steinmaurer
> IB LogManager - The Logging Tool for Interbase
> http://www.equitania.de/interbase/iblogmanager/start.htm (German)
> http://www.equitania.de/interbase/iblogmanager/default.htm (English)
> E-Mail:     mailto:iblogmana...@aon.at

> "Kjell-?ke Boberg" <k...@connova.se> schrieb im Newsbeitrag
> news:3AA87577.877BD21B@connova.se...
> > Here is an example SQL

> > select first_name ||' ' || last_name Fullname
> > from employee

> > Use the || to concatenate. In this example I also add a blank character
> ( ' ' )
> > between first and last name. After the concatenation you can optionally
> > give the "new" field a name ( Fullname in this example).

> > Good Luck

> > --Kjell-?ke Boberg
> > ConNova Systems AB
> > Motala Sweden

> > Matthew Sutton wrote:

> > > I was wondering if any of the SQL gurus out there can help.

> > > I want to create a view over a table which will concatinate two fields
> into
> > > one. eg....

> > > Combine field GivenName & field Surname to create a field Name.

> > > Can this be done???

> > > Any help would be appreciated.

> > > Thanks in advance

> > > Matthew Sutton
> > > National Systems Development Manager
> > > VIPS International Pty Ltd

Other Threads