Board index » delphi » concatenating fields SQL Statement

concatenating fields SQL Statement

Greetings

i'm having a problem writing a query that can be used both on Microsoft SQL
and MySQL.
my goal is to concatenate 2 fields and make it as one to the resultset ...

Microsoft SQL accept's this and returns the right string
    SELECT Nom + '' '' + Prenom AS Name FROM Tbl
MySQL returns '0.00'

now ... MySQL accepts this format..and returns the right string
    SELECT CONCAT(CONCAT(Nom, ' '), Prenom) AS Name FROM Tbl
but Microsoft SQL generates an error.

is there a common way to do this ?

the why of this concatenation is that we use the same table to identify
individuals and companies.
but in the case of the company only the field nom is being used...

thnak you
Sandro .

 

Re:concatenating fields SQL Statement


Quote
> is there a common way to do this ?

It's somewhat database specific, but a double-pipe symbol works on a lot of
databases.  Example:

select 'Eric' || ' ' || 'Hill' from sometable;

VALUE
--------------
Eric Hill

Re:concatenating fields SQL Statement


na .. both of them reject it ...
i guess that i wond have a choice but to detect wich database i'm using .
and load the query strings from a ressource file or a dll ...

thnaks.
Sandro

Quote
"Eric Hill" <e...@ijack.net> wrote in message

news:3e95deb7$1@newsgroups.borland.com...
Quote
> > is there a common way to do this ?

> It's somewhat database specific, but a double-pipe symbol works on a lot
of
> databases.  Example:

> select 'Eric' || ' ' || 'Hill' from sometable;

> VALUE
> --------------
> Eric Hill

Re:concatenating fields SQL Statement


The SQL Standard way is

SELECT 'AAA' || 'BBB'...

SQL Server should accept this. I do not know about MySQL.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Other Threads