Shulou(Shulou.com)06/01 Report--

String function:

The string functions in postgresql are: calculating string length function, string merging function, string replacement function, string comparison function, finding specified string position function, and so on.

1. Functions for calculating string number and string length: char_length (str) and length (str)

Char_length (str) returns the number of characters contained in the string str. A multi-byte character counts as a single character.

Example: use the char_length function to calculate the number of characters in a string, such as:

Testdb=# select char_length ('date'), char_length (' zhang')

Char_length | char_length

-+-

4 | 5

(1 row)

Length (str) returns the byte length of a string. When using the utf8 encoded character set, a Chinese character is 3 bytes, and a number or letter counts as a byte.

Testdb=# select length ('date'), length (' zhang')

Length | length

-+-

4 | 5

(1 row)

Note: the calculation result of the length function is the same as that of the char_length function, because the number of English characters is the same as the number of bytes occupied, and one character accounts for one byte.

2. Merge string functions: concat (s _ 1 ~ 2), concat_ws (x _ journal s _ 1 ~ 2)

Concat (S1 and S2) returns a string that results in a connection parameter. If any parameter is null, the return value is null. If all parameters are non-binary strings, the result is a non-binary string. If the argument contains any binary string, the result is a binary string.

Concat_ws is a separator from other parameters.

Example: use the concat function to concatenate the string 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 specified length: left (SMagnen) and right (SMagnen)

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

Example: use the left function to return the five characters that begin on the left side of the string, such as

Testdb=# select left ('football',5)

Left

-

Footb

(1 row)

Right (sforce n) returns the rightmost character of the string s

Example: use the right function to return the right-hand character in a string, such as:

Testdb=# select right ('football',4)

Right

-

Ball

(1 row)

4. Functions to fill the string: lpad (S1) and rpad (S1)

Lpad (S1) returns the string S1, whose left side is filled by the character S2 and the filling length is len. If the length of S1 is greater than len, the return value is shortened to the len character.

Example: use the lpad function to populate the string, as follows:

Testdb=# select lpad ('hello',4,'??'), lpad (' hello',10,'??')

Lpad | lpad

-+-

Hell |? hello

(1 row)

Rpad returns the string S1, whose right side is filled to the length of the len character by the string S2. 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 to delete spaces: ltrim (s), rtrim (s) and trim (s)

Ltrim (s) returns the string s and the space character on the left side of the string is deleted.

Example: use the ltrim function to remove the space to the left of the string, such as:

Testdb=# select'(book)', concat ('(', ltrim ('book'),')

? column? | concat

-+-

(book) | (book)

(1 row)

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

Example: use the rtrim function to delete the space to the right of the string, such as:

Testdb=# select'(book)', concat ('(', rtrim ('book'),')

? column? | concat

-+-

(book) | (book)

(1 row)

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

Example: use the trim function to delete spaces at both 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 s1.s1 at both ends of the string s as optional, and removes spaces without being specified.

Example: use the trim (S1 from s) function to delete the characters specified at both ends of the string, such as:

Testdb=# select trim ('zh' from' zhanghellzhnihao')

Btrim

-

Anghellzhnihao

(1 row)

7. The function that repeatedly generates a string: repeat (spendium n)

Repeat (sforce n) returns a string consisting of the repeating string s, where n represents the number of times it is repeatedly generated. If n

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.