RDBMS, as a safe, shared and reliable software management system for managing data, needs to organize and store data according to different data models. In order to facilitate the processing of data for users, any database provides rich processing functions for different data types in order to simplify the complexity of business development.In addition to supporting functions supported by the SQL standard, PostgreSQL can also use different server programming interfaces to implement custom functions for different business needs.Today, let's introduce the string handler in PostgreSQL.

1. String Processing Functions

1.1 String Operator

PostgreSQL supports the common symbol || in relational databases for string operators, which are used to connect between multiple strings or between empty and multiple strings.
Example:

hrdb=# -- || is a string connection operator
hrdb=# SELECT 'Postgre' || 'SQL' AS result;
   result   
------------
 PostgreSQL
(1 row)

hrdb=# SELECT 'PostgreSQL' || ' ' || 'is most advanced open source ORDBMS !' AS result;
                      result                      
--------------------------------------------------
 PostgreSQL is most advanced open source ORDBMS !
(1 row)

1.2 String Function

String Bit Length Function
bit_length(string)
Returns the number of bits of a string in a database
Example:

hrdb=# --String Bit Function bit_length(string)
hrdb=# SELECT bit_length('PostgreSQL') AS result;
 result 
--------
     80
(1 row)

Statistical Character Length Function
char_length(string) or character_length(string)
Returns the number of characters passed in
Example:

hrdb=# --String length function char_length() or character_length()
hrdb=# SELECT char_length('PostgreSQL') AS result;
 result 
--------
     10
(1 row)

hrdb=# SELECT character_length('PostgreSQL 12') AS result;
 result 
--------
     13
(1 row)

Reminder:
Note that using this function to count string length will include whitespace characters, and if you need to exclude whitespace characters from the statistics, you will need to do the appropriate processing.

Case Conversion Functions
lower(string) and upper(string)
Returns the conversion of the incoming string to large (small) write
Example:

hrdb=# --Case conversion functions lower() and upper
hrdb=# SELECT lower('POSTGRESQL') AS result;
   result   
------------
 postgresql
(1 row)

hrdb=# SELECT upper('postgresql') AS result;
   result   
------------
 POSTGRESQL
(1 row)

Convert first letter of string to uppercase function
initcap(string)
Returns a string in which the first letter of the incoming string is uppercase
Example:

hrdb=# --String initials uppercase function initcap()
hrdb=# SELECT initcap('postgreSQL') AS result;
   result   
------------
 Postgresql
(1 row)

Replace Substring Function
overlay()
Returns the starting to ending position replaced by the specified substring
Example:

hrdb=# --Replaces characters from start to end with specified characters
hrdb=# SELECT overlay('Postgresql' placing 'SQL' from 8 for 10);
  overlay   
------------
 PostgreSQL
(1 row)
hrdb=# --If no end position is specified using this function, it will be based on the specified string length
hrdb=# --Replace
hrdb=# SELECT overlay('http://www.google.com' placing 'https' from 1);
        overlay        
-----------------------
 https//www.google.com
(1 row)

String Occupancy Byte Statistics Function
octet_length()
Returns the length of bytes occupied by a string
Example:

--Returns the byte length function of a string octet_length()
SELECT octet_length('PostgreSQL') AS result;
SELECT octet_length('Postgresql') AS result;

Reminder:
For Chinese characters, PostgreSQL uses UTF8 encoding by default, which takes up 3 bytes per Chinese character.

Find the position function of a specified character or string in a string
position()
Example:

hrdb=# --Find the position function of a specified character or string in a string
hrdb=# SELECT position('pos' in '$PGDATA/postgresql') AS result;
 result 
--------
      9
(1 row)

Intercepts a specified character or string function at a specified location
substring() or substr()
Example:

hrdb=# --intercepts the specified character or string function substring() or substr() at the specified position
hrdb=# SELECT substring('https://www.baidu.com',9) AS result;
    result     
---------------
 www.baidu.com
(1 row)

hrdb=# SELECT substr('https://www.baidu.com',9,21) AS result;
    result     
---------------
 www.baidu.com
(1 row)

Reminder:

Typically, the position and substring functions are used together to facilitate batch processing of strings.

Such as extracting fields https://www.baidu.com InWww.baidu.comIf the string is short and easy to count, it is not necessary to use the position function to locate the starting position of the specified string. If the string is long and not easy to count, the position function is required to locate the starting position of the specified string as a substring to intercept string letters.The starting position of the number.
Example:

hrdb=> SELECT substring('https://www.baidu.com',position('w' in 'https://www.baidu.com')) as result;
    result     
---------------
 www.baidu.com

Remove redundant character functions at both ends of a string
trim([leading | trailing|both] [characters] from string)
Where parameters
Leading | trailing | box representation
remove
Start | End | Characters at both ends, default to both
leading is equivalent to the ltrim() function, meaning left removal
trailing is equivalent to the rtrim() function, meaning right removal
Both is equivalent to the btrim() function, meaning removal at both ends
Example:

hrdb=> --Remove redundant character functions at both ends of a string trim()
hrdb=> --Where parameters leading | trailing | both Express
hrdb=> --Remove Start | Ending | String at both ends, defaulted to both
hrdb=> --leading Amount to ltrim()Function for left removal
hrdb=> --trailing Amount to rtrim()Function for right removal
hrdb=> SELECT trim('rdb ms' from 'rdb PostgreSQL ms') AS result;
   result   
------------
 PostgreSQL
(1 row)

hrdb=> SELECT trim(leading 'rdb ms' from 'rdb PostgreSQL ms') AS result;
    result     
---------------
 PostgreSQL ms
(1 row)

hrdb=> SELECT trim(trailing 'rdb ms' from 'rdb PostgreSQL ms') AS result;
     result     
----------------
 rdb PostgreSQL
(1 row)

hrdb=> SELECT ltrim('rdb PostgreSQL ms','rdb ms') AS result;
    result     
---------------
 PostgreSQL ms
(1 row)

hrdb=> SELECT rtrim('rdb PostgreSQL ms','rdb ms') AS result;
     result     
----------------
 rdb PostgreSQL
(1 row)

Character Conversion to ASCII Code Function
ascii()
Returns the ascii code value of the specified character
Example:

hrdb=> --Returns the specified character's ASCII Code-valued function ascii()
hrdb=> SELECT ascii('S') AS result;
 result 
--------
     83
(1 row)

hrdb=> SELECT ascii('a') AS result;
 result 
--------
     97
(1 row)

Replace String Function
replace()
Returns the substitution of a character that appears in a string with a specified substring
Example:

Hrdb=> -- Returns the substitution function replace() that uses the specified substring for the character that appears in the string
hrdb=> SELECT replace('http://www.baidu.com','http','https') AS result;
        result         
-----------------------
 https://www.baidu.com
(1 row)

Hrdb=> SELECT replace (wind direction in Nanjing blowing south,'south','north') AS result;
       result       
--------------------
 The wind in Beijing is blowing north
(1 row)

Convert ascii code values corresponding to characters to character functions
chr()
Returns the character corresponding to the specified ascii code
Example:

hrdb=> --Returns the specified ascii Code Corresponding Character Function chr()
hrdb=> -- 39 Represents a single quotation mark 
hrdb=> SELECT chr(39) || first_name || chr(39) AS first_name
hrdb-> FROM employees limit 1;
 first_name 
------------
 'Steven'
(1 row)

hrdb=> -- 10 Represents a line break symbol
hrdb=> SELECT replace(t.txt,chr(10),' ') as result 
hrdb-> FROM 
hrdb-> (SELECT 'PostgreSQL
hrdb'> is most
hrdb'> advanced open source rdbms
hrdb'> ' as txt) as t;
                     result                     
------------------------------------------------
 PostgreSQL is most advanced open source rdbms 
(1 row)

hrdb=> -- 32 Represents a space symbol
hrdb=> SELECT replace(t.txt,chr(32),'|') 
hrdb-> FROM 
hrdb-> (SELECT 'a        b' AS txt) t;
  replace   
------------
 a||||||||b
(1 row)

Reminder:
chr(9) stands for a horizontal tab, chr(11) for a vertical tab, and chr(13) for a carriage return symbol.Usually the chr() function is used in conjunction with the replace() function, as shown in the example above.

String Stitching Function
concat()
Split any string, including null values
Example:

hrdb=> --String Stitching Function concat()
hrdb=> SELECT concat('https',null,'://','www','.','google','.','com') AS result;
         result         
------------------------
 https://www.google.com

Split multiple strings and separate functions according to specified format
concat_ws()
Using the first parameter as the delimiter, separate strings according to how the first parameter is delimited.null separates the string as the first argument and is ignored.
Example:

hrdb=> --String Splicing Separator Function concat_ws()
hrdb=> SELECT concat_ws(chr(9),'Huawei','5885H') AS result;
    result     
---------------
 Huawei  5885H
(1 row)

Convert String Encoding Function
convert(string,src_encoding,dest_encoding)
Converts a string to a different encoding, src_encoding represents source encoding, dest_encoding represents the encoding to be converted
Example:

hrdb=> --String Encoding Conversion Function
hrdb=> SELECT convert('postgresql','UTF8','LATIN1') AS result;
         result         
------------------------
 \x706f737467726573716c
(1 row)

Encoding and Decoding Functions
decode() and encode()
Converts the specified string (or binary data type) to a binary data type (or string)
Example:

hrdb=> --Codec function encode() decode()
hrdb=> SELECT decode(md5('PostgreSQL'),'base64') AS result;
                       result                       
----------------------------------------------------
 \xdfdf5b77579ee7cef6e3979c69ce9fdfd6de69af7df3ce9f
(1 row)

hrdb=> SELECT encode('\xdfdf5b77579ee7cef6e3979c69ce9fdfd6de69af7df3ce9f','base64') AS result;
              result              
----------------------------------
 399bd1ee587245ecac6f39beaa99886f
(1 row)

Reminder:
The codec formats are base64,hex,escape

printf
format()
Output string in C-like format
Example:

hrdb=> --printf format()
hrdb=> SELECT format('PostgreSQL %s %2$s %3$s ','is','most','popular','database') AS result;
           result            
-----------------------------
 PostgreSQL is most popular 
(1 row)

Returns the string to the left of the specified location
left()
Returns a string to the left of the specified position, intercepted from the right if the specified position is a negative number
Example:

hrdb=> --Returns the character function to the left of the specified position string left()
hrdb=> SELECT left('https://www.baidu.com',5) AS result;
 result 
--------
 https
(1 row)

hrdb=> SELECT left('https://www.baidu.com',-13) AS result;
  result  
----------
 https://
(1 row)

Returns the string to the right of the specified bit
left()
Returns a string to the right of the specified location, intercepted from the left if the specified location is a negative number
Example:

hrdb=> --Returns the character function to the right of the specified position string left()
hrdb=> SELECT right('https://www.baidu.com',13) AS result;
    result     
---------------
 www.baidu.com
(1 row)

hrdb=> SELECT right('https://www.baidu.com',-5) AS result;
      result      
------------------
 ://www.baidu.com

String Fill Function
lpad() and rpad() represent left and right padding
Returns a character that exceeds the length of the string itself and will be filled with the specified character
Example:

--String Fill Function lpad() rpad()
--Will employees In table first_name Column set to right alignment
--take salary Column set to left alignment
hrdb=> SELECT lpad(first_name,15,'-') as first_name,
hrdb-> rpad(salary::varchar,10,'+') as salary
hrdb-> FROM employees LIMIT 2;
   first_name    |   salary   
-----------------+------------
 ---------Steven | 24000.00++
 ----------Neena | 17000.00++

Specify position string delimiter function
split_part()
Separates a string from a specified location and returns a string separated by a specified location
Example:

hrdb=> --Specify position string delimiter function
hrdb=> SELECT split_part('https://www.baidu.com','//',2) AS result;
    result     
---------------
 www.baidu.com
(1 row)

Replace functions one-to-one in character units
translate()
Replace characters in a string one by one in character units
Example:

hrdb=> --Replace functions one-to-one in character units
hrdb=> SELECT translate('Postgr2e3S4QL','123456789','') AS result;
   result   
------------
 PostgreSQL
(1 row)

Practical Application of 1.3 String Processing Function

Cycle out each character in the string.When it comes to traversing strings, you know that in a procedural language, such as PL/SQL in Oracle or PL/PGSQL in PostgreSQL, traversing strings is easy and can be done with a for loop, but what can you do to achieve a cyclic output of strings in a SQL statement?

Example: Traverse PostgreSQL loop output

hrdb=> SELECT substring(t1.txt,t2.id) AS col1,
hrdb->        substring(t1.txt,char_length(t1.txt) - t2.id + 1) AS col2,
hrdb->        left(t1.txt,t2.id) AS col3,
hrdb->        right(t1.txt,t2.id) AS col4
hrdb-> FROM
hrdb->   (SELECT 'PostgreSQL' AS txt) t1
hrdb-> JOIN
hrdb->   (SELECT id
hrdb(>    FROM generate_series(1,10) id) t2 ON (t2.id != 0);
    col1    |    col2    |    col3    |    col4    
------------+------------+------------+------------
 PostgreSQL | L          | P          | L
 ostgreSQL  | QL         | Po         | QL
 stgreSQL   | SQL        | Pos        | SQL
 tgreSQL    | eSQL       | Post       | eSQL
 greSQL     | reSQL      | Postg      | reSQL
 reSQL      | greSQL     | Postgr     | greSQL
 eSQL       | tgreSQL    | Postgre    | tgreSQL
 SQL        | stgreSQL   | PostgreS   | stgreSQL
 QL         | ostgreSQL  | PostgreSQ  | ostgreSQL
 L          | PostgreSQL | PostgreSQL | PostgreSQL
(10 rows)

Author: Song Shaohua

PostgreSQL Branch trains members of the Certification Committee, Chief Technical Expert of Shengshu Science and Technology, Gold Teacher of Shengshu Mathematics Institute, oracle 11g OCM, and the first PGCE of PostgreSQL.

Has served in the construction of large data platform for the State Grid JiBei Electric Power Co., Ltd., built IT basic services for the Social Security Bureau and the Beijing Health and Planning Commission, and built web for many banks and securities companiesServer, system and database maintenance; IT training experience in government and private enterprises such as tax bureaus, State Grid, banks; design DW data warehouse models for related security industries; use PostgreSQL,Greenplum,HUAWEIGaussDB,Vertica and lickhouse as data base services; develop TB-level data landing programs and 100TB-level data migration programs.