Concat two fields in SQL

[thomascraig]

I am trying to concatenate two columns in an SQL statement but haven't been
having much success: here is the portion of the SQL: CONCAT(people_lname||'
'||people_fname) as Person

Is this correct syntax?

Reading through one of my oracle books it seems to be the correct syntax but
not sure if CF likes it.

[paross1]

What database are you using? With Oracle you would use || as the concatenation
operator (without the CONCAT), but for SQL Server or Access you would use + as
in

people_lname +' '+ people_fname as Person

Phil

[thomascraig]

I'm using an Oracle db, I tried your suggestion without any luck.

[thomascraig]

Phil,

Just remembered I'm using SQL Server in my testing environment - oracle in production. Your suggestion worked.

[Dan Bracuk]

You're using SQL Server in testing and Oracle in development? You realize that all of your testing is suspect, right?

[paross1]

If you are using SQL Server in develoment, and Oracle in production, and you
have lot of SQL, it is going to be a minefield of little syntax and function
differences even if you closely adhere to "standard" ANSI SQL 92, and you will
constantly have to make many many changes when migrating your development code
into production. A very bad idea. I know that Oracle is expensive but couldn't
your DBA create a small instance for you to do your development and testing?

Phil

[MikerRoo]

Doesn't Oracle have a free edition, Oracle express, just for situations like this?

[paross1]

Yup. Never used it, but it sounds like it would be much better than SQL server
for dev, and Oracle for production!

[url]http://www.oracle.com/technology/products/database/xe/index.html[/url]

Oracle Database 10g Express Edition (Oracle Database XE) is an entry-level,
small-footprint database based on the Oracle Database 10g Release 2 code base
that's free to develop, deploy, and distribute; fast to download; and simple to
administer. Oracle Database XE is a great starter database for:

Developers working on PHP, Java, .NET, and Open Source applications
DBAs who need a free, starter database for training and deployment
Independent Software Vendors (ISVs) and hardware vendors who want a starter
database to distribute free of charge
Educational institutions and students who need a free database for their
curriculum

Phil