PostgreSQL string function

Source: Internet
Author: User
Tags lowercase md5 hash postgresql rtrim alphanumeric characters truncated

Function return type Describe Example Results
string | | String Text String connection ' Post ' | | ' Gresql ' PostgreSQL
Bit_length (String) Int The number of bits in a string Bit_length (' Jose ') 32
Char_length (String) Int Number of characters in a string Char_length (' Jose ') 4
Convert (string using Conversion_name) Text Changes the encoding using the specified transformation name. Convert (' PostgreSQL ' using Iso_8859_1_to_utf8) ' PostgreSQL '
Lower (String) Text Convert a string to lowercase Lower (' TOM ') Tom
Octet_length (String) Int Number of bytes in a string Octet_length (' Jose ') 4
Overlay (string placing string from int [for int]) Text Replace a sub-string Overlay (' Txxxxas ' placing ' hom ' from 2 for 4) Thomas
Position (substring in string) Int The location of the specified sub-string Position (' Om ' in ' Thomas ') 3
SUBSTRING (string [from int] [for int]) Text Extract sub-string SUBSTRING (' Thomas ' from 2 for 3) Hom
SUBSTRING (string from pattern) Text Extracting substrings that match POSIX regular expressions SUBSTRING (' Thomas ' from ' ... $ ') Mas
SUBSTRING (string from pattern for escape) Text Extracting substrings that match a SQL regular expression SUBSTRING (' Thomas ' from '% # ' o_a# ' _ ' for ' # ') Oma
Trim ([leading | trailing | both] [characters] from string) Text The longest string containing only characters (the default is a blank) from the beginning/end/sides of string strings Trim (both ' x ' from ' Xtomxx ') Tom
Upper (String) Text Converts the string to uppercase. Upper (' Tom ') TOM
ASCII (text) Int The ASCII code of the first character of the parameter ASCII (' x ') 120
Btrim (string text [, characters text]) Text Removes the longest string of characters that are contained only in characters (the default is white space) from the beginning and end of a string Btrim (' Xyxtrimyyx ', ' xy ') Trim
Chr (int) Text The character that gives the ASCII code Chr (65) A
Convert (string text, [src_encoding name,] dest_encoding name) Text Convert a string to dest_encoding Convert (' Text_in_utf8 ', ' UTF8 ', ' LATIN1 ') Text_in_utf8 in ISO 8859-1 encoding
Initcap (text) Text Capitalize the first child of each word, and keep the other lowercase. A word is a series of alphanumeric characters, separated by non-alphanumeric numbers. Initcap (' Hi Thomas ') Hi Thomas
Length (string text) Int The number of characters in string Length (' Jose ') 4
Lpad (string text, length int [, fill text]) Text Fills the string as length by filling the character fill (which is blank by default). If the string is longer than length, it is truncated (on the right). Lpad (' Hi ', 5, ' XY ') Xyxhi
LTrim (string text [, characters text]) Text Removes the longest string containing only characters (the default is a blank) from the beginning of the string string. LTrim (' Zzzytrim ', ' xyz ') Trim
MD5 (string text) Text Computes the MD5 hash of the string, returning the result in hexadecimal. MD5 (' abc ')
Repeat (string text, number int) Text Repeats a string number number times. Repeat (' Pg ', 4) Pgpgpgpg
Replace (string text, from text, to text) Text Replaces all occurrences of a string in a string with a substring from the byte string to. Replace (' Abcdefabcdef ', ' CD ', ' XX ') Abxxefabxxef
Rpad (string text, length int [, fill text]) Text Fills the string as length by filling the character fill (which is blank by default). If the string is longer than length, it is truncated. Rpad (' Hi ', 5, ' XY ') Hixyx
RTrim (string text [, character text]) Text Removes the longest word containing only character (the default is a blank) from the end of the string string RTrim (' trimxxxx ', ' x ') Trim
Split_part (string text, delimiter text, field int) Text Returns the generated field substring (1 Base) based on the delimiter delimited string. Split_part (' [Email protected][email protected]~ghi ', ' [email protected]~ ', 2) Def
Strpos (string, substring) Text The position of the declared substring. Strpos (' High ', ' IG ') 2
SUBSTR (String, from [, Count]) Text Extracts a substring. substr (' Alphabet ', 3, 2) Ph
To_ascii (text [, encoding]) Text Converts text from another encoding to ASCII. To_ascii (' Karel ') Karel
To_hex (number Int/bigint) Text Converts number to its corresponding hexadecimal representation. To_hex (9223372036854775807) 7fffffffffffffff
Translate (string text, from text, to text) Text Converts any character contained in a string to a character in the From, to the corresponding character in to. Translate (' 12345 ', ' + ', ' ax ') a23x5

PostgreSQL string function

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.