This lesson shows how to format the results of your query:
- show a value depending on a condition with “select case”.
- show a part of a string with “mid”.
- convert results with “cast”.
- concatenate strings with “concat” or “+”.
- take off spaces from a string with “ltrim” and “rtrim”.
- stuff characters with “lpad” or “rpad”.
- round numerical values with “round”.
- calculate the difference between two dates with “datediff”.
“select case” helps to show different values (values that are not coming from any table) in the results depending on a set of conditions.
Example: we have the following table registering revenues per city and per year:
We want to show the string “high” for revenues over 30000, “medium” for revenues between 20000 and 30000 and "low" when lower than 20000. We display this information together with the city and the year.
Mid: Select only a part of a string
The purpose of mid is to show only a part of a string.
To show only letters starting from the second letter in the column city, and show only three letters, we use the following syntax:
“cast” converts the result of a query to another type. Example: values in transaction_id are of type integer and you need to convert these values to strings of maximum size 3.
In this query: we take the table column “transaction_id” of type integer and we display it on the results column named “trans_id_char” of type char(3). The interest of this is that you can concatenate this result with another string (which you cannot do with numbers). This is shown in the following section about concat().
The result shows that the resulting result column's name is trans_id_char:
concat concatenates two or more strings (when using sql server as a DBMS, we just use “+” between two strings to concatenate them). Strings will appear side by side on the results.
In this query, transaction_id is shown on the left of the string “Transaction ID” inside one column in the results.
rtrim( ), ltrim( ) :
rtrim() takes off all the spaces on the right hand side of the string.
ltrim() takes off all the spaces on the left hand side of the string.
Example: a field in the column first_name has spaces before and after the client’s first name.
In this example we use concat to put “--” before and after the string in order to see the spaces:
|-- anuragh --|
Here is the result when using rtrim and ltrim (two right columns):
|-- anuragh --||--anuragh --||-- anuragh--|
The result with ltrim does not have any space on the left, while the result with rtrim does not have any space on the right.
In order to add several times the character ‘x’ at the beginning of the string until the string becomes 6 characters long, the syntax is the following:
Example: if the town is “aba”, then lpad(town,6,’x’) is xxxaba.
To add the characters at the end of the string, use rpad:
Example: if the town is “aba”, then rpad(town,6,’x’) is abaxxx.
To round-up numbers, use the round function. You write the number you want to round-up and how many digits after the decimal point should be left after rounding.
if revenue=1.567, then:
select round(revenue,0) shows 1
select round(revenue,2) shows 1.56
Result without round:
Result with round:
datediff calculates the difference (in days) between two dates.
Example: show for each row in sales the difference between now and the transaction date:
|now()||transaction_date||DATEDIFF( now(), transaction_date)|
Note that the number is negative when the date on the left hand side is earlier than the date on the right hand side.