Board index » delphi » How do I concatenate fields in Interbase SQL?

Daniel Grace

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

How do I concatenate fields in Interbase SQL?

I want to select the concatenation of two character based fields, but
Interbase SQL does not like the "+" and seems expect numeric fields e.g.
SELECT char_field1 + char_field2 FROM some_table;
This command raises Interbase error 413: conversion from string.
Is there another operator or function that will acheive the concatenation?

SELECT char_field1, char_field2 FROM some_table;
just wont' cut it in this particular case as the procedure I am using
works from a single field parameter & I would rather not have to
re-write this:
IbQry : TIBQuery
...
some_str_var := IbQry2.Fields[0].AsString;

Thanks,

Daniel.

 
Steve Lee-Woo

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:How do I concatenate fields in Interbase SQL?


Don't know about Interbase, but in MySQL you concatenate by using comma-
separated identifiers rather than using a + e.g.

SELECT CONCAT(Lastname, '  ', Initials) FROM person;

AFAIK, this is standard SQL, so it should work in Interbase.

In article <A0G65.12885$Hm2.21869@NewsReader>, dgr...@encrypta.com
says...

Quote
> I want to select the concatenation of two character based fields, but
> Interbase SQL does not like the "+" and seems expect numeric fields e.g.
> SELECT char_field1 + char_field2 FROM some_table;
> This command raises Interbase error 413: conversion from string.
> Is there another operator or function that will acheive the concatenation?

> SELECT char_field1, char_field2 FROM some_table;
> just wont' cut it in this particular case as the procedure I am using
> works from a single field parameter & I would rather not have to
> re-write this:
> IbQry : TIBQuery
> ...
> some_str_var := IbQry2.Fields[0].AsString;

> Thanks,

> Daniel.

--
Stephen Lee-Woolf
Academic Information Services
University of Salford
Salford, Manchester
Martijn Tonie

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:How do I concatenate fields in Interbase SQL?


Hi there,

InterBase uses the SQL 92 standard there: the double pipe

select char_field1 || ' ' || char_field2 from some_table

However, when one of the fields is NULL it results in a NULL value.
--

Martijn Tonies
Upscene Productions

For InterBase Workbench,
the developer tool for InterBase
http://www.interbaseworkbench.com

For Delphi Components and InterBase tools:
http://www.upscene.com

Quote
"Daniel Grace" <dgr...@encrypta.com> wrote in message

news:A0G65.12885$Hm2.21869@NewsReader...
Quote
> I want to select the concatenation of two character based fields, but
> Interbase SQL does not like the "+" and seems expect numeric fields e.g.
> SELECT char_field1 + char_field2 FROM some_table;
> This command raises Interbase error 413: conversion from string.
> Is there another operator or function that will acheive the concatenation?

> SELECT char_field1, char_field2 FROM some_table;
> just wont' cut it in this particular case as the procedure I am using
> works from a single field parameter & I would rather not have to
> re-write this:
> IbQry : TIBQuery
> ...
> some_str_var := IbQry2.Fields[0].AsString;

> Thanks,

> Daniel.

Bruce Biddl

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:How do I concatenate fields in Interbase SQL?


In Oracle you can use vertical bars in your SQL statement like this

SELECT ||Field1||Field2||' '||Field3||'sometext'||Field4
FROM SOMETABLE

it doesn't care if numeric and string are mixed it just gives you a string !

Quote
Daniel Grace <dgr...@encrypta.com> wrote in message

news:A0G65.12885$Hm2.21869@NewsReader...
Quote
> I want to select the concatenation of two character based fields, but
> Interbase SQL does not like the "+" and seems expect numeric fields e.g.
> SELECT char_field1 + char_field2 FROM some_table;
> This command raises Interbase error 413: conversion from string.
> Is there another operator or function that will acheive the concatenation?

> SELECT char_field1, char_field2 FROM some_table;
> just wont' cut it in this particular case as the procedure I am using
> works from a single field parameter & I would rather not have to
> re-write this:
> IbQry : TIBQuery
> ...
> some_str_var := IbQry2.Fields[0].AsString;

> Thanks,

> Daniel.

Bruce Biddl

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:How do I concatenate fields in Interbase SQL?


Never had a problem with that null thing in Oracle Martin !!
If so you he could always try the NVL function if available in interbase.

Select NVL(FieldOrValue1,FieldOrValue2)
etc
if FieldOrValue1 is null then FieldOrValue2 is returned

Regards
Bruce

Quote
Martijn Tonies <m.tonies@stopspam_upscene.com> wrote in message

news:962307192.9361.0.pluto.d4ee4209@news.demon.nl...
Quote
> Hi there,

> InterBase uses the SQL 92 standard there: the double pipe

> select char_field1 || ' ' || char_field2 from some_table

> However, when one of the fields is NULL it results in a NULL value.
> --

> Martijn Tonies
> Upscene Productions

> For InterBase Workbench,
> the developer tool for InterBase
> http://www.interbaseworkbench.com

> For Delphi Components and InterBase tools:
> http://www.upscene.com
> "Daniel Grace" <dgr...@encrypta.com> wrote in message
> news:A0G65.12885$Hm2.21869@NewsReader...
> > I want to select the concatenation of two character based fields, but
> > Interbase SQL does not like the "+" and seems expect numeric fields e.g.
> > SELECT char_field1 + char_field2 FROM some_table;
> > This command raises Interbase error 413: conversion from string.
> > Is there another operator or function that will acheive the
concatenation?

> > SELECT char_field1, char_field2 FROM some_table;
> > just wont' cut it in this particular case as the procedure I am using
> > works from a single field parameter & I would rather not have to
> > re-write this:
> > IbQry : TIBQuery
> > ...
> > some_str_var := IbQry2.Fields[0].AsString;

> > Thanks,

> > Daniel.

Other Threads

1. Concatenating field on Interbase

2. concatenating fields SQL Statement

3. Concatenating two fields in an sql statement

4. String concatenating in Interbase

5. Saving TMemo field data to a Blob field (Interbase Table) via SQL Update Query

6. String concatenating in Interbase

7. String concatenating with Interbase

8. Lookup fields with concatenated keys...

9. Concatenate Selected fields

10. Concatenate 2 text fields in SP