PostgreSQL String Functions
String Functions
The PostgreSQL string functions can be used for manipulating and examining various type of queries independently or within a table. Here is the list of string functions in PostgreSQL.
List of String Functions
| Functions | Syntax | Example | Output |
|---|---|---|---|
| bit_length() | bit_length(<string>) | bit_length('w3resource') | 80 |
| char_length or character_length () | char_length(<string>) or character_length(<string>) | char_length('w3resource') | 10 |
| lower() | lower(<string>) | lower('W3RESOURCE') | w3resource |
| octet_length() | octet_length(<string>) | octet_length('w3resource') | 10 |
| overlay() | overlay(<main_string> placing <replacing_string> from <starting_position> [ for <mumber_of_characters> ]) |
overlay('w3333333rce' placing 'resou' from 3 for 4) | w3resou33rce |
| position() | position('search_string' in 'main_string') | position('our' in 'w3resource') | 6 |
| substring() | substring(<main_string> from <extracting_position> for <extracting_characters> ) | SELECT substring('w3resource' from 4 for 5) | esour |
| trim() | trim([leading|trailing|both] <removing_string> from <main_string> ) | trim(' w3resource ') | w3resource |
| upper() | upper(<string>) | upper('w3resource') | W3RESOURCE |
| ascii() | ascii(<string>) | ascii('w3resource') | 119 |
| btrim() | btrim(<string>[ , <text_trim>]) | btrim('settw3resourcesttes', 'test') | w3resourc |
| chr() | chr(<int>) | chr(90) | Z |
| concat() | concat(<string1>,[<string2>,<string3>,...]) | concat('w',3,'r', 'esource','.','com'); | w3resource.com |
| initcap() | initcap(<string>) | initcap('RABINDRANATH TAGORE') | Rabindranath Tagore |
| left() | left(<string>,<no_of_characters>) | left('w3resource',3) | w3r |
| length() | length(<string>) | length('w3resource') | 10 |
| lpad() | lpad(<string>,<length>[,<fill_text>]) | lpad('esource', 10, 'w3r') | w3resourc |
| ltrim() | ltrim(<string>[, <triming_text>]) | ltrim('testltrim', 'best'); | ltrim |
| pg_client_ encoding() |
pg_client_encoding (string) |
pg_client_encoding() | UTF8 |
| quote_ident() | quote_ident(<string>) | quote_ident('SELECT * FROM employee') | "SELECT * FROM employee" |
| repeat() | repeat(<string>,<repeating_number>) | repeat('*--*', 2); | *--**--* |
| replace() | replace(<string>,<matching_string>,<replace_with>) | replace('test string', 'st', '**'); | te** **ring |
| rpad() | rpad(<string>, length, [<fill_text >]) | rpad('w3r', 10, 'esource'); | w3resource |
| rtrim() | rtrim(<string> [, < trimming_text >]) | rtrim('rtrimtest', 'best'); | rtrim |
| split_part() | split_part(<string>,<delimiter>, <field_number>) | split_part('ordno-#-orddt-#-ordamt', '-#-', 2); | orddt |
| strpos() | strpos(<string>, < substring >) | strpos('w3resource', 'so'); | 5 |
| substr() | substr(<string>,<position_from > [,<number_of_characters>]) |
substr('w3resource',2,3); | 3re |
| translate() | translate(<string>,<matching_string>, <replace_string>]) |
translate('translate', 'rnlt', '123'); | 1a2s3ae |
| right() | right(<string>,n) | right('w3resource',5); | ource |
| reverse() | reverse(<string>) | reverse('w3resource'); | ecruoser3w |
Previous: String Operators
Next: BIT_LENGTH function