Format your query results with SQL functions

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

“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:

cityyearrevenue
achalpur201410000.12
achhnera201420023.34
adalaj201410222.99
agra201330456.23
ahmedabad201350055.50
ahmednagar201410000.00
akola201340098.34

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.

Syntax:

select city,
year,
case when revenue>30000 then 'high'
when revenue between 20000 and 30000 then 'medium'
else 'low'
end as level
from sales

Result:

cityyearlevel
achalpur2014low
achhnera2014medium
adalaj2014low
agra2013high
ahmedabad2013high
ahmednagar2014low
akola2013high

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:

select mid(city,2,3) as test from sales

Result:

test
cha
chh
dal
gra
hme
hme
kol

cast:

“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.

select cast(transaction_id as char(3)) trans_id_char from sales

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:

trans_id_char
1
2
3
4
5
6
7

concat:

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.

Example:

select concat("Transaction ID:",cast(transaction_id as char(3))) as title from sales

Result:

title
Transaction ID:1
Transaction ID:2
Transaction ID:3
Transaction ID:4
Transaction ID:5
Transaction ID:6
Transaction ID:7

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:

select concat('--',first_name,'--') as name from clients
where client_id = 15

Result:

name
-- anuragh --

Here is the result when using rtrim and ltrim (two right columns):

select concat('--',first_name,'--') as name_notrim,concat('--',ltrim(first_name),'--') as name_ltrim, concat('--',rtrim(first_name),'--') as name_rtrim from clients
where client_id = 16

Result:

name_notrimname_ltrimname_rtrim
-- 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.

lpad, rpad:

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:

select lpad(town,6,'x') from sales

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:

select rpad(town,6,'x') from sales

Example: if the town is “aba”, then rpad(town,6,’x’) is abaxxx.

round:

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

Example:

Result without round:

cityyearrevenue
achalpur201410000.12
achhnera201420023.34
adalaj201410222.99
agra201330456.23
ahmedabad201350055.50
ahmednagar201410000.00
akola201340098.34

Result with round:

select city,year,round(revenue,0) from sales

Result:

cityyearround(revenue,0)
achalpur201410000
achhnera201420023
adalaj201410223
agra201330456
ahmedabad201350056
ahmednagar201410000
akola201340098

Datediff:

datediff calculates the difference (in days) between two dates.

Example: show for each row in sales the difference between now and the transaction date:

select now(),transation_date,DATEDIFF( now(), transaction_date ) from sales

Result:

now()transaction_dateDATEDIFF( now(), transaction_date)
2014-10-29 03:41:262014-11-13-15
2014-10-29 03:41:262014-07-06115
2014-10-29 03:41:262014-03-09234
2014-10-29 03:41:262013-01-12655
2014-10-29 03:41:262013-04-13564
2014-10-29 03:41:262014-08-1476
2014-10-29 03:41:262013-10-05389

Note that the number is negative when the date on the left hand side is earlier than the date on the right hand side.

Previous Post:

Combine Update and Delete with Subqueries in SQL

 
Share on:



Author:
Last udpated: September 10, 2017
Created: October 28, 2014