SQL Tutorials

When running more complex queries, such as concatenating strings, often times you may need to trim whitespace from the values.

Let’s look at an example of this. Remember the query we ran for concatenating:

SELECT CONCAT (productName, ” , ” , price) FROM products;

As it is the output would be XBOX , $399

Let’s say we wanted to trim the whitespace between XBOX and the comma.

Here’s the query:

SELECT CONCAT (RTRIM(productName) ” , ” , price) FROM products;

In this case we used rtrim because we wanted to trim to the right or immediately following the value. If we wanted to trim space before the value we would use the LTRIM, and if we wanted to trim from both sides, we would simply use trim.

Posted February 16, 2008 Comments(0)

A word of warning about concatenating with sql, if the data used is one that is created by a user where query strings are created dynamically, there could be a possibility of an sql injection. This method is best used for outputting information you have in a database. For example, let’s say you have a products table and you want to concatenate the product name and price.

SELECT CONCAT (productName, ” , ” , price) FROM products;

The output would be: XBOX, $399

You can see the power in this function. We could just as well add some text modifiers:

SELECT CONCAT (productName, ” , “”Buy it Now,” , price) FROM products;

The output would be: XBOX, Buy it Now, $399

The Concat operator is used mainly by mysql. Other database engines such as Oracle use the “||” operator.

Posted February 16, 2008 Comments(2)