Lpad function in Postgresql and string function in general

--------------
LPAD
----------------
select lpad(name,8,'X')
select lpad('abc',8,'X')

LPAD(input_string,length, input string) prepeding text values in string
--------
SUBSTRING
--------
The following example extracts the house number (maximum 4 digits, from 0 to 9) from a string:
SELECT
	SUBSTRING (
		'The house no. is 9001',
		'([0-9]{1,4})'
	) as house_no

substring(string from start_position for length);
SUBSTRING ( string ,start_position , length )

SELECT
	SUBSTRING ('PostgreSQL' FROM 1 FOR 8); -- PostgreS
SELECT
	SUBSTRING ('PostgreSQL' FROM 8); -- SQL

------------
LENGTH(string);
--------------
SELECT
	LENGTH ('PostgreSQL Tutorial'); -- 19
SELECT
	LENGTH (''); -- 0
SELECT
	LENGTH (' '); -- 1

select *, substring(email,1,strpos(email,'@')-1) as user_name,
  substring(email,strpos(email,'@')+1,length(email)) as domain_name
from emailtraining as e; 

---------------
  LEFT
LEFT(string, n) 
--------------
SELECT LEFT(first_name, 1) initial,
    COUNT(*)
FROM customer
GROUP BY initial
ORDER BY initial;

In this example, first, the LEFT() function returns initials of all customers. Then, the GROUP BY clause groups customers by their initials. Finally, the COUNT() function returns the number of customer for each group.


---------
  REGEXP MATCHES
----------
REGEXP_MATCHES(source_string, pattern [, flags])
1) source
The source is a string that you want to extract substrings that match a regular expression.

2) pattern
The pattern is a POSIX regular expression for matching.

3) flags
The flags argument is one or more characters that control the behavior of the function. For example, (i) allows you to match case-insensitively.

SELECT 
    REGEXP_MATCHES('Learning #PostgreSQL #REGEXP_MATCHES #2123', 
         '#([A-Za-z0-9_]+)', 
        'g');

The following statement uses the REGEXP_MATCHES() function to get films whose descriptions contain the word Cat or Dog:

SELECT 
	film_id, 
	title , 
	description, 
	REGEXP_MATCHES(description, 'Cat | Dog ') cat_or_dog
FROM 
	film
ORDER BY title;

--------
REGEXP REPLACE
-----------
REGEXP_REPLACE(source, pattern, replacement_string,[, flags])   
SELECT REGEXP_REPLACE('John Doe','(.*) (.*)','\2, \1');