-------------- 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');
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter