postgresql--String Functions

String Functions:

The string functions in PostgreSQL are: Calculate string length functions, string merge functions, string substitution functions, string comparison functions, find specified string position functions, and so on.

1. Functions to count string characters and string lengths: Char_length (str) and Length (str)

The Char_length (str) return value is the number of characters that the string str contains. A multibyte character counts as a single character.

Example: Use the Char_length function to calculate the number of string characters, such as:

testdb=# Select Char_length (' Date '), Char_length (' Zhang ');

Char_length | Char_length

-------------+-------------

4 | 5

(1 row)

Length (str) returns a string of byte lengths, when using the UTF8 encoding character set, a Chinese character is 3 bytes, and a number or letter counts as one byte.

testdb=# Select Length (' Date '), Length (' Zhang ');

Length | Length

--------+--------

4 | 5

(1 row)

Note: The length function evaluates to the same as the Char_length function because the number of English characters is the same as the bytes occupied, and one character occupies one byte.

2. Merging String functions: Concat (S1,S2,,,,), Concat_ws (x,s1,s2,,,,)

Concat (s1,s2,,,) returns the string that results from the connection parameter. Any one parameter is null, and the return value is null. If all arguments are non-binary strings, the result is a non-binary string. If the argument contains any twos string, the result is a binary string.

Concat_ws (x,s1,s2,,,) x is a delimiter with other parameters.

Example: Use the CONCAT function to concatenate strings, as follows:

testdb=# Select Concat (' PostgreSQL ', ' 9.6 '), concat (' PostgreSQL ', null, ' TestDB ');

concat | Concat

---------------+------------------

postgresql9.6 | Postgresqltestdb

(1 row)

Example: Use the CONCAT_WS function to concatenate delimited strings, such as:

testdb=# Select Concat_ws (' _ ', ' PostgreSQL ', ' 9.6 '), CONCAT_WS (' * * ', ' PostgreSQL ', null, ' TestDB ');

Concat_ws | Concat_ws

----------------+--------------------

postgresql_9.6 | Postgresql**testdb

(1 row)

3. The function to get a string of the specified length: Left (s,n) and Right (S,n)

Left (S,n) returns the leftmost n characters from the beginning of the string s.

Example: Use the Left function to return the first 5 characters in a string, such as

testdb=# Select Left (' Football ', 5);

Left

-------

Footb

(1 row)

Right (S,n) returns the rightmost character of the string s

Example: Use the right function to return the characters to the left of a string, such as:

testdb=# Select Right (' Football ', 4);

Right

-------

Ball

(1 row)

4. Functions to populate strings: Lpad (S1,LEN,S2) and Rpad (S1,LEN,S2)

Lpad (S1,LEN,S2) returns the string S1, whose left is populated by the character S2, the padding length is Len, and the length of the join S1 is greater than Len, then the return value is shortened to the Len character.

Example: Use the Lpad function to populate a string with the following:

testdb=# Select Lpad (' Hello ', 4, '?? '), Lpad (' Hello ', 10, '?? ');

Lpad | Lpad

------+------------

Hell |????? Hello

(1 row)

Rpad (S1,LEN,S2) returns the string S1, whose right is filled by the string S2 to the Len character length. If the length of the string is greater than Len, the return value is shortened to the same length as the Len character.

Example: Use the Rpad function to populate a string, such as:

testdb=# Select Rpad (' Hello ', 4, '? '), Rpad (' Hello ', 10, '? ');

Rpad | Rpad

------+------------

Hell | Hello?????

(1 row)

5. Functions for removing spaces: LTrim (s), RTrim (s), and trim (s)

LTrim (s) returns the string s, and the left space character of the string is deleted.

Example: Use the LTrim function to remove spaces to the left of a string, such as:

testdb=# Select ' (book) ', Concat (' ('), LTrim (' book '), ') ');

? column? | Concat

----------+---------

(book) | (book)

(1 row)

RTrim (s) returns the string s, and the right space character of the string is deleted.

Example: Use the RTrim function to remove spaces to the right of a string, such as:

testdb=# Select ' (book) ', Concat (' ('), RTrim (' book '), ') ');

? column? | Concat

----------+---------

(book) | (book)

(1 row)

Trim (s) removes the space on both sides of the string s.

Example: Use the TRIM function to remove spaces at the ends of a specified string, such as:

testdb=# Select ' (book) ', Concat (' ('), Trim (' book '), ') ');

? column? | Concat

----------+--------

(book) | (book)

(1 row)

6. Delete the function of the specified string: Trim (S1 from S)

Trim (S1 from s) removes all substrings in the string s s1.s1 as optional, removing spaces if not specified.

Example: Use the trim (S1 from S) function to remove characters specified at both ends of a string, such as:

testdb=# Select trim (' zh ' from ' Zhanghellzhnihao ');

Btrim

----------------

Anghellzhnihao

(1 row)

7. Function of repeating string: repeat (s,n)

Repeat (s,n) returns a string consisting of a repeating string s, and n represents the number of times a duplicate is generated. Returns an empty string if n<=0, or null if s or n is null.

Example: Use the Repeat function to repeatedly generate the same string, such as:

testdb=# Select repeat (' Zhang ', 3);

Repeat

-----------------

Zhangzhangzhang

(1 row)

8. Replace function: replace (S,S1,S2)

Replace (S,S1,S2) uses the string S2 to replace all string S1 in the string s.

Example: Use the Replace function for string substitution operations.

testdb=# Select replace (' www.baidu.com ', ' W ', ' Z ');

Replace

---------------

Zzz.baidu.com

(1 row)

9, get the function of substring: substring (s,n,len)

SUBSTRING (S,n,len) indicates that a substring of length len is returned from the string s, starting at position N. You may also use a negative value for N. If so, the position of the substring starts at the character of N at the end of the string, which is the nth character of the countdown.

testdb=# Select substring (' Zhanghello ', 5) as a1,substring (' Zhanghello ', 5,3) as a2,substring (' Lunch ', -3) as A3;

A1 |  A2 | A3

--------+-----+-------

Ghello | Ghe | Lunch

(1 row)

Note: If you use a value that is less than 1 for Len, the result is always the entire string.

10. function matching substring start position: position (str1 in str)

The role of the position (str1 in str) function is to return the starting position of the substring str1 in the string str.

Example: Use the position function to find a string specifying STR1 at the beginning of a string, such as:

testdb=# Select position (' Ball ' in ' football ');

Position

----------

5

(1 row)

11, String Reverse function: reverse (s)

Reverse (s) reverses the string s, and the order of the returned strings is reversed in the S-character order.

Example: Use the reverse function to reverse a string, such as:

testdb=# Select reverse (' abc ');

Reverse

---------

Cba

(1 row)

postgresql--String Functions