Regexp function in postgresql

PHOTO EMBED

Thu Apr 28 2022 07:39:37 GMT+0000 (Coordinated Universal Time)

Saved by @Bambibo9799

REGEXP_REPLACE(source, pattern, replacement_string,[, flags])   
1) source

The source is a string that replacement should be taken place.

2) pattern

The pattern is a POSIX regular expression for matching substrings that should be replaced.

3) replacement_string

The replacement_string is a string that to replace the substrings which match the regular expression pattern.

4) flags

The flags argument is one or more character that controls the matching behavior of the function e.g., i allows case-insensitive matching, n enables matching any character and also the newline character.
--------------------------------
*	a sequence of 0 or more matches of the atom
+	a sequence of 1 or more matches of the atom
^	matches at the beginning of the string
$	matches at the end of the string

\d	matches any digit, like [[:digit:]] -- seperti nomor 1-9
\s	matches any whitespace character, like [[:space:]]
\w	matches any word character, like [[:word:]]
\D	matches any non-digit, like [^[:digit:]] --- seperti A-Z
\S	matches any non-whitespace character, like [^[:space:]]
\W	matches any non-word character, like [^[:word:]]
--------------------------------------------------------------------------------------------
select version, regexp_replace("version",'[^0-9]+','') as number,
regexp_replace("version",'[\D]+','') as number_ver2,
regexp_replace("version",'[\d]+','') as string_ver1,
regexp_replace("version",'[^\w]+','') as tes, --selain string and number, yang lain kosong
regexp_replace("version",'[^\D]+','') as string_ver2, --selain string, yang lain kosong
regexp_replace("version",'[^\d]+','') as number_vers3, --selain number, yang lain kosong
regexp_replace("version",'[^a-zA-Z]+', '') as name -- selain aplhabet a-z A-Z, yang lain kosong
 from game_csv as gc;
----------------------------------------------------------------------------------------------
select regexp_replace('jasdoasd.asdas.,asdsa.','[^a-z]','','g');-- replace semua yang bukan huruf menjadi kosong
select regexp_replace('jasdoasd.asdas.,asdsa.','[a-z]','','g'); --replace semua huruf a-z menjadi kosong
---------------------------------------------------------------------------------------------------
SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
  foo   
-------
 the    
 quick  
 brown  
 fox    
 jumps 
 over   
 the    
 lazy   
 dog

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
              regexp_split_to_array             
-----------------------------------------------
 {the,quick,brown,fox,jumps,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
 foo 
-----
 t         
 h         
 e         
 q         
 u         
 i         
 c         
 k         
 b         
 r         
 o         
 w         
 n         
 f         
 o         
 x         
(16 rows)
content_copyCOPY