A Gentle Introduction to

SQL

GISQ: How to use functions

Most SQL implementations offer a large number of functions to manipulate strings and perform complex calculations. Unfortunately each implementation has its own suite with different names.

Concatenate strings.
Substring: Extracting part of a string.
lower case
Finding a substring in a string
Formatting numbers to two decimal places.
Replace a NULL with a specific value
Conditional values
Get the date and time right now.
Format dates.


Concatenate strings. Concatenation means "stick strings together". In this example we concatenate three strings, name and region are string attributes of the table, ' is in ' is a string literal. access variations: None	SELECT name & ' is in ' & region FROM cia WHERE name LIKE 'D%'	postgres oracle access mysql sqlserver

Substring: Extracting part of a string. Here we take two characters starting from position 4. 123456789 'Afghanistan' -> 'ha' 'China' -> 'na' 'Sri Lanka' -> ' L' access variations: None	SELECT name, MID(name,4,2) FROM cia WHERE region = 'Asia'	postgres oracle access mysql sqlserver

lower case UPPER CASE is similar. access variations: LCase and UCase. Note that when testing strings case is ignored.	SELECT LCase(name) FROM cia WHERE UCase(region)='SOUTH AMERICA'	postgres oracle access mysql sqlserver

Finding a substring in a string Here we extract the first word of a country name. INSTR gives this position of one string within another, we use this and substring to pick out the first few characters. access variations: None	SELECT name, INSTR(name, ' '), MID(name,1,INSTR(name,' ')) FROM cia WHERE name LIKE '% %'	postgres oracle access mysql sqlserver

Formatting numbers to two decimal places. This rounds up or down access variations: None	SELECT title, score, FORMAT(score,'.00') FROM movie WHERE id <= 10	postgres oracle access mysql sqlserver

Replace a NULL with a specific value access variations: Use IIF and ISNULL	SELECT code, name, IIF(ISNULL(leader), 'NO LEADER RECORDED!', leader) FROM party	postgres oracle access mysql sqlserver

Conditional values access variations: None	SELECT title, score, IIF(score>8.5,'Excellent','OK') FROM movie WHERE id<=10	postgres oracle access mysql sqlserver

Get the date and time right now. access variations: None	SELECT Date() FROM t_dual	postgres oracle access mysql sqlserver

Format dates. access variations: None	SELECT Format(Now(),'DD MMM YYYY HH:M') FROM t_dual	postgres oracle access mysql sqlserver