Scribd
Upload
6 views3 pages

String Functions in PostgreSQL Explained

The document provides a list of string manipulation functions along with their descriptions, examples, and results. Functions include ASCII, CHR, CONCAT, LENGTH, LOWER, and others, each serving specific purposes for handling strings. This serves as a reference for users needing to perform various string operations in a programming context.

Uploaded by

A Arifyan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views3 pages

String Functions in PostgreSQL Explained

The document provides a list of string manipulation functions along with their descriptions, examples, and results. Functions include ASCII, CHR, CONCAT, LENGTH, LOWER, and others, each serving specific purposes for handling strings. This serves as a reference for users needing to perform various string operations in a programming context.

Uploaded by

A Arifyan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Function Description Example Result

Return the ASCII code value of a character or


ASCII ASCII(‘A’) 65
Unicode code point of a UTF8 character
Convert an ASCII code to a character or a
CHR CHR(65) ‘A’
Unicode code point to a UTF8 character
CONCAT Concatenate two or more strings into one CONCAT(‘A’,’B’,’C’) ‘ABC’
CONCAT_WS Concatenate strings with a separator CONCAT_WS(‘,’,’A’,’B’,’C’) ‘A,B,C’
‘Hello
FORMAT Format arguments based on a format string FORMAT(‘Hello %s’,’PostgreSQL’)
PostgreSQL’
INITCAP Convert words in a string to title case INITCAP(‘hI tHERE’) Hi There
LEFT Return the first n character in a string LEFT(‘ABC’,1) ‘A’
LENGTH Return the number of characters in a string LENGTH(‘ABC’) 3
LOWER Convert a string to lowercase LOWER(‘hI tHERE’) ‘hi there’
Pad on the left a a string with a character to a
LPAD LPAD(‘123′, 5, ’00’) ‘00123’
certain length
Remove the longest string that contains
LTRIM specified characters from the left of the input LTRIM(‘00123’) ‘123’
string
MD5 Return MD5 hash of a string in hexadecimal MD5(‘ABC’)
POSITION Return the location of a substring in a string POSTION(‘B’ in ‘A B C’) 3
SELECT
Match a POSIX regular expression against a
REGEXP_MATCHES REGEXP_MATCHES(‘ABC’, ‘^(A) {A,BC}
string and returns the matching substrings
(..)$’, ‘g’);
Replace substrings that match a POSIX REGEXP_REPLACE(‘John Doe’,'(.*)
REGEXP_REPLACE ‘Doe, John’
regular expression by a new substring (.*)’,’\2, \1′);
REPEAT Repeat string the specified number of times REPEAT(‘*’, 5) ‘*****’
Replace all occurrences in a string of
REPLACE REPLACE(‘ABC’,’B’,’A’) ‘AAC’
substring from with substring to
REVERSE Return reversed string. REVERSE(‘ABC’) ‘CBA’
Return last n characters in the string.
RIGHT When n is negative, return all but first |n| RIGHT(‘ABC’, 2) ‘BC’
characters.
Pad on the right of a string with a character to
RPAD RPAD(‘ABC’, 6, ‘xo’) ‘ABCxox’
a certain length
Remove the longest string that contains
RTRIM specified characters from the right of the input RTRIM(‘abcxxzx’, ‘xyz’) ‘abc’
string
Split a string on a specified delimiter and
SPLIT_PART SPLIT_PART(‘2017-12-31′,’-‘,2) ’12’
return nth substring
SUBSTRING Extract a substring from a string SUBSTRING(‘ABC’,1,1) A’
Remove the longest string that contains
TRIM specified characters from the left, right or both TRIM(‘ ABC ‘) ‘ABC’
of the input string
UPPER Convert a string to uppercase UPPER(‘hI tHERE’) ‘HI THERE’

You might also like