Regexp function in postgresql
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)
Comments