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 suit 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

Concatenate strings. Concatenation means "stick strings together". In this example we concatenate three strings, name and region are string attributes of the table, ' is a country ' is a string literal.	SELECT CONCAT(name, ' is a country in ', region) FROM cia WHERE name LIKE 'D%'	oracle access mysql
Substring: Extracting part of a string. SUBSTRING	SELECT name, SUBSTRING(name,4,2) FROM cia WHERE region = 'Asia'	oracle access mysql
lower case UPPER CASE is similar.	SELECT LOWER(name) FROM cia WHERE UPPER(region)='SOUTH AMERICA'	oracle access mysql
Finding a substring in a string Here we extract the first word of a country name	SELECT name, INSTR(name, ' '), SUBSTRING(name,1,INSTR(name,' ')) FROM cia WHERE name LIKE '% %'	oracle access mysql
Formatting numbers to two decimal places. This rounds up or down	SELECT title, score, FORMAT(score,2) FROM movie WHERE id <= 10	oracle access mysql
Replace a NULL with a specific value	SELECT code, name, NVL(leader, 'NO LEADER RECORDED!') FROM party	oracle access mysql
Conditional values	SELECT title, score, IF(score>8.5,'Excellent','OK') FROM movie WHERE id<=10	oracle access mysql