Functions

ABS Function:
ABS returns the absolute value. The output is positive even if the input is negative:

  ABS(x) = x if x>=0
 ABS(x) = -x if x<0 data-blogger-escaped-pre="pre">    ABS can be useful for testing values that are "close". For example   this query shows each country that has area that is roughly   70 thousand. The value 70000 is the target value, 500 is the "tolerance"   so the test ABS(area-70000)<500 data-blogger-escaped-69500="69500" data-blogger-escaped-70000-500="70000-500" data-blogger-escaped-70000="70000" data-blogger-escaped-and="and" data-blogger-escaped-area="area" data-blogger-escaped-between="between" data-blogger-escaped-br="br" data-blogger-escaped-is="is" data-blogger-escaped-tests="tests" data-blogger-escaped-that="that" data-blogger-escaped-the="the">
SELECT name, area FROM bbc
 WHERE ABS(area-70000)<500 data-blogger-escaped-br="br">Result:

name
 
area
 
 
Georgia
 
69700
 
 
Ireland
 
70182


CASE
CASE allows you to return different values under different conditions.      

If there no conditions match (and there is not ELSE) then NULL is      returned.

  CASE WHEN condition1 THEN value1
      WHEN condition2 THEN value2 
      ELSE def_value
 END

SELECT name, population
     ,CASE WHEN population<1000000 data-blogger-escaped-br="br" data-blogger-escaped-small="small" data-blogger-escaped-then="then">           WHEN population<10000000 data-blogger-escaped-br="br" data-blogger-escaped-medium="medium" data-blogger-escaped-then="then">           ELSE 'large'
      END
AS result

 FROM bbc where population like '3%' order by population asc ;

this query will list name, population and the result of CASE. It will consider only population
starting with number 3 and will order the results by ascending order of population.

Result:


name
 
population
 
result
 
 
Monaco
 
32000
 
small
 
 
Liechtenstein
 
34000
 
small
 
 
Bahamas
 
321000
 
small
 
 
The Maldives
 
338000
 
small
 
 
Brunei
 
374000
 
small
 
 
Malta
 
397000
 
small
 
 
Armenia
 
3000000
 
medium