SQL : Internal Functions

Getting Started with PostgreSQL :
https://dev117uday.gitbook.io/databases/sql/getting-started

Loading Sample Data Guide :
https://dev117uday.gitbook.io/databases/sql/getting-started/load-data

Internal Functions

Order of execution of SQL statements

FROM
WHERE
SELECT
ORDER BY

Concatenation Operator

select concat(first_name,last_name) as full_name 
    from directors limit 10;

   full_name    
----------------
 TomasAlfredson
 PaulAnderson
 WesAnderson

select concat_ws(' ',first_name,last_name) as full_name 
    from directors limit 3;

    full_name    
-----------------
 Tomas Alfredson
 Paul Anderson
 Wes Anderson

if you can have a null value in column, always use concat_ws because it will place nothing in that and and also not place the spacer like | or a space

Type Conversion

Type of Conversion	Notes
Implicit	data conversion is done AUTOMATICALLY
Explicit	data conversion is done via 'conversion functions' eg. `CAST` or `::`

SELECT * FROM movies;

-- exact datatype match : no conversion
SELECT * FROM movies WHERE movie_id = 1;

-- Implicit conversion : conversion
SELECT * FROM movies WHERE movie_id = '1';

-- Explicit conversion : conversion
SELECT * FROM movies WHERE movie_id = integer '1';

-- Output of all queries above
 movie_id |     movie_name     | movie_length | movie_lang | release_date | age_certificate | director_id 
----------+--------------------+--------------+------------+--------------+-----------------+-------------
        1 | A Clockwork Orange |          112 | English    | 1972-02-02   | 18              |          13

Casting

-- CAST function
-- Syntax : CAST ( expression as target_data_type );

SELECT CAST ( '10' AS INTEGER );

 int4 
------
   10

SELECT 
    CAST ('2020-02-02' AS DATE), 
    CAST('01-FEB-2001' AS DATE);

    date    |    date    
------------+------------
 2020-02-02 | 2001-02-01

SELECT 
    CAST ( 'true' AS BOOLEAN ), 
    CAST ( '1' AS BOOLEAN ), 
    CAST ( '0' AS BOOLEAN );

 bool | bool | bool 
------+------+------
 t    | t    | f

SELECT CAST ( '14.87789' AS DOUBLE PRECISION );

  float8  
----------
 14.87789

SELECT '2020-02-02'::DATE , '01-FEB-2001'::DATE;

    date    |    date    
------------+------------
 2020-02-02 | 2001-02-01

SELECT '2020-02-02 10:20:10.23'::TIMESTAMP;

       timestamp        
------------------------
 2020-02-02 10:20:10.23

SELECT '2020-02-02 10:20:10.23 +05:30'::TIMESTAMPTZ;

        timestamptz        
---------------------------
 2020-02-02 04:50:10.23+00

SELECT 
    '10 minute'::interval, 
    '10 hour'::interval, 
    '10 day'::interval, 
    '10 week'::interval, 
    '10 month'::interval;

 interval | interval | interval | interval | interval 
----------+----------+----------+----------+----------
 00:10:00 | 10:00:00 | 10 days  | 70 days  | 10 mons

SELECT 
    20! AS "result 1" , 
    CAST( 20 AS bigint ) ! AS "result 2";

      result 1       |      result 2       
---------------------+---------------------
 2432902008176640000 | 2432902008176640000

SELECT 
    ROUND(10,4) AS "result 1", 
    ROUND ( CAST (10 AS NUMERIC) ) AS "result 2", 
    ROUND ( CAST (10 AS NUMERIC) , 4 ) AS "result 3";

 result 1 | result 2 | result 3 
----------+----------+----------
  10.0000 |       10 |  10.0000

SELECT 
    SUBSTR('12345',2) AS "RESULT 1", 
    SUBSTR( CAST('12345' AS TEXT) ,2) AS "RESULT 2";

 RESULT 1 | RESULT 2 
----------+----------
 2345     | 2345

CREATE TABLE ratings (
    rating_id SERIAL PRIMARY KEY,
    rating VARCHAR(2) NOT NULL
);

INSERT INTO ratings ( rating ) 
VALUES ('A'), ('B'), ('C'), ('D'), (1), (2), (3), (4);

SELECT 
    rating_id,
    CASE 
        WHEN rating~E'^\\d+$' THEN
            CAST ( rating as INTEGER )
        ELSE
            0
        END AS rating
FROM
    ratings;

 rating_id | rating 
-----------+--------
         1 |      0
         2 |      0
         3 |      0
         4 |      0
         5 |      1
         6 |      2
         7 |      3
         8 |      4

Formatting Functions

https://www.postgresql.org/docs/12/functions-formatting.html

to_char()

Refer to the documentation

PostgreSQL to_char

SELECT TO_CHAR (
    100870,
    '9,999999'
);    

  to_char  
-----------
    100870

SELECT 
    release_date, 
    TO_CHAR(release_date,'DD-MM-YYYY'),
    TO_CHAR(release_date,'Dy, MM, YYYY') 
FROM 
    movies LIMIT 3;

 release_date |  to_char   |    to_char    
--------------+------------+---------------
 1972-02-02   | 02-02-1972 | Wed, 02, 1972
 1979-08-15   | 15-08-1979 | Wed, 08, 1979
 2001-01-04   | 04-01-2001 | Thu, 01, 2001

SELECT 
    TO_CHAR ( 
        TIMESTAMP '2020-01-01 13:32:30', 
        'HH24:MI:SS' 
    );

 to_char  
----------
 13:32:30

to_number()

SELECT TO_NUMBER(
    '1420.89', '9999.'
);

 to_number 
-----------
      1420

SELECT TO_NUMBER(
    '10,625.78-', '99G999D99S'
);

 to_number 
-----------
 -10625.78

SELECT TO_NUMBER(
    '$1,625.78+', '99G999D99S'
);

 to_number 
-----------
   1625.78

SELECT to_number(
    '$1,420.65' , 'L9G999D99'
);

 to_number 
-----------
   1420.65

SELECT to_number(
    '21,420.65' , '99G999D99'
);

 to_number 
-----------
  21420.65

to_date()

SELECT TO_DATE( '2020/10/22' , 'YYYY/MM/DD' );

  to_date   
------------
 2020-10-22

SELECT to_date( '022199' , 'MMDDYY' );

  to_date   
------------
 1999-02-21

SELECT to_date( 'March 07, 2019' , 'Month DD, YYYY' );

    to_date   
------------
 2019-03-07

to_timestamp()

SELECT TO_TIMESTAMP(
    '2017-03-31 9:30:20',
    'YYYY-MM-DD HH:MI:SS'
);

      to_timestamp      
------------------------
 2017-03-31 09:30:20+00

SELECT
    TO_TIMESTAMP('2017     Aug','YYYY MON');

      to_timestamp      
------------------------
 2017-08-01 00:00:00+00

String Functions

Upper(string)
Lower(string)
INITCAP(string)
REVERSE(string)
LPAD(string)
RPAD(string)
LENGTH(string)
CHAR_LENGTH(string) : Same as Length
POSITION( string in string )
STRPOS ( , < substring > )
SUBSTRING (string , length)
REPLACE (string, from_string, to_string)

SELECT INITCAP(first_name) as FirstName,
       INITCAP(last_name)  as LastName
FROM directors
LIMIT 3;

 firstname | lastname
-----------+-----------
 Tomas     | Alfredson
 Paul      | Anderson
 Wes       | Anderson

SELECT LEFT('Uday', 3), RIGHT('Uday', 3);

 left | right
------+-------
 Uda  | day

SELECT LEFT('Uday', -3), RIGHT('Uday', -3);

 left | right
------+-------
 U    | y

SELECT REVERSE('UDAY YADAV');

  reverse
------------
 VADAY YADU

SELECT SPLIT_PART('1,2,3,4', ',', 1), 
       SPLIT_PART('1|2|3|4', '|', 2);

 split_part | split_part
------------+------------
 1          | 2

SELECT TRIM(LEADING FROM '  Amazing PostgreSQL'),
       TRIM(TRAILING FROM 'Amazing PostgreSQL  '),
       TRIM('  Amazing PostgreSQL  ');

       ltrim        |       rtrim        |       btrim
--------------------+--------------------+--------------------
 Amazing PostgreSQL | Amazing PostgreSQL | Amazing PostgreSQL


SELECT TRIM(LEADING '0' FROM CAST(0001245 AS TEXT));

 ltrim
-------
 1245


SELECT LTRIM('yummy', 'y'),
       RTRIM('yummy', 'y'),
       BTRIM('yummy', 'y');

 ltrim | rtrim | btrim
-------+-------+-------
 ummy  | yumm  | umm


SELECT upper('uday yadav'),  initcap('uday yadav');

   upper    |  initcap
------------+------------
 UDAY YADAV | Uday Yadav

SELECT INITCAP(first_name) as FirstName,
       INITCAP(last_name)  as LastName
FROM directors LIMIT 3;

 firstname | lastname
-----------+-----------
 Tomas     | Alfredson
 Paul      | Anderson
 Wes       | Anderson

SELECT LEFT('Uday', 3), RIGHT('Uday', 3);
 left | right
------+-------
 Uda  | day

SELECT LEFT('Uday', -3), RIGHT('Uday', -3);
 left | right
------+-------
 U    | y


SELECT LPAD('Database', 15, '*'),
       RPAD('Database', 15, '*');

      lpad       |      rpad
-----------------+-----------------
 *******Database | Database*******

SELECT LENGTH('Uday Yadav');

 length
--------
     10

SELECT LENGTH(CAST(10013 AS TEXT));
 length
--------
      5


SELECT char_length(''),
       char_length('  '),
       char_length(NULL);

 char_length | char_length | char_length
-------------+-------------+-------------
           0 |           2 |

SELECT first_name || ' ' || last_name as FullName,
       LENGTH(first_name || ' ' || last_name)
                                      as FullNameLength
FROM Directors
ORDER BY 2 DESC LIMIT 2;

             fullname              | fullnamelength
-----------------------------------+----------------
 Florian  Henckel von Donnersmarck |             33
 Francis Ford Coppola              |             20

SELECT POSITION('Amazing' IN 'Amazing PostgreSQL'),
       POSITION('is' IN 'This is a computer');
 position | position
----------+----------
        1 |        3

SELECT STRPOS('World Bank', 'Bank');
 strpos
--------
      7

SELECT first_name,
       last_name
FROM directors
WHERE strpos(last_name, 'on') > 0 LIMIT 3;

 first_name | last_name
------------+-----------
 Tomas      | Alfredson
 Paul       | Anderson
 Wes        | Anderson

SELECT substring('What a wonderful world' from 1 for 10);

 substring
------------
 What a won

SELECT repeat('A', 4), repeat(' ', 9), repeat('.', 8);
 repeat |  repeat   |  repeat
--------+-----------+----------
 AAAA   |           | ........

SELECT REPLACE('ABC XYZ', 'XY', 'Z');
 replace 
---------
 ABC ZZ