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() 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 >
substr('w3resource',2,3); 3re
translate() translate(<string>,<matching_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

Follow us on Facebook and Twitter for latest update.

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook