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), as in:

people_lname || ' ' || people_fname as Person

for SQL Server or Access you would use + as in:

people_lname +' '+ people_fname as Person

MySQL you might do it this way:

CONCAT(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

[Guest]

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!

Oracle Database 10g Express Edition

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