Sunday, September 28, 2008

Code Nuggets: Getting two SQL column values in a single column

Well this type of thing doesn't come up very frequently (at least to me) and if you're like me working on multiple databases it gets very frustrating when it does come up. Since I don't use it very frequently, I tend to forget how to do it properly in the database system I am using at the time.

So it goes something like this: Suppose you have a simple SQL table called Users. Now this table has three columns, id (int), firstName (varchar) and lastName (varchar). Sometimes you have to return the full name from the database in a single column (for reasons of sanity, or otherwise). How do you do it depends on your DBMS. (We assume that we have a record (1, 'George', 'Lucas') in the table.)

MySQL:

MySQL supports it via the CONCAT function. You can have as many values as you want as the parameters to the function.

Example:
SELECT CONCAT(firstName, LastName) from Users

would return:
GeorgeLucas

and would even be better if you use a CONCAT(firstName, " ", LastName) in its place which would give you a nice space in between the names.

Oracle:

Oracle also supports the CONCAT function, but you are restricted to only two values as parameters. However you can CONCAT the CONCAT function itself on the cost of getting your code ugly. Another way of concatenating multiple strings in Oracle is actually using the || operators.

Example:
SELECT CONCAT(firstName, CONCAT(" ", lastName)) from Users

or

SELECT firstName || " " || lastName from Users

both accomplish the same thing and would return:

George Lucas

SQL Server:

With SQL Server it actually gets a little bit easier if you are used to concatenate your strings through the '+' operator. However make sure that you CAST any numeric values to varchar before trying to concatenate it, as '+' also works as the addition operator.

Example:
SELECT firstName + " " + lastName from Users

That's all to it to the concatenation function, and remember that selecting two values and displaying them as a single values is also called concatenation (a thing that I usually look over).

No comments: