PostgreSQL occupies an important place in database management with a rich set of built-in functions. Among these, the PostgreSQL string function is particularly noteworthy because of its flexibility and adaptability to text data in a database. For developers working on any product, software, or technology related to data binding, the familiar PostgreSQL string function can be very useful. In this tutorial, we will learn about a list of PostgreSQL String functions and their usage.
PostgreSQL String Functions
String functions in PostgreSQL are powerful tools designed to manipulate and handle text data efficiently. This function plays an important role in data management and analysis by providing a wide range of compatibility functions. From simple tasks like searching for a long string with LENGTH() to more complex operations like matching patterns using LIKE and regular expressions, these tasks make users capable of selecting, modifying and managing information effectively.
PostgreSQL’s string operator facilitates operations such as concatenating strings using CONCAT(), which is important for concatenating text objects. Functions such as TRIM() and REPLACE() allow data to be cleaned and transformed, and ensure data accuracy and integrity. The PostgreSQL string function is the basic architecture for processing entries in a database environment, handling multiple operations, and ensuring data integrity and quality.
List of PostgreSQL String Functions
1. ASCII
The ASCII(text) function returns the ASCII code of the first character of the text. It works for characters with numeric values from 0 to 255.
Example:
SELECT ASCII('Z');Code language: SQL (Structured Query Language) (sql)Output:
2. CONCAT
The CONCAT() function connects two or more strings and returns a single string created by taking multiple string arguments formed by concatenating them.
Example:
SELECT CONCAT('Postgre', 'SQL');Code language: SQL (Structured Query Language) (sql)Output:
3. CHR
The CHR() function is used to return the character with the specified ASCII code.
Example:
SELECT CHR(80);Code language: SQL (Structured Query Language) (sql)Output:
4. FORMAT
The format() function is used to format a string according to the specified parameters. Particularly useful when you want to dynamically create strings based on variables and other data.
Example:
SELECT format('Hello, %s!', 'John');Code language: SQL (Structured Query Language) (sql)Output:
5. LEFT
The LEFT() function is used to extract a specified number of characters from the beginning of the string.
Example:
SELECT LEFT('AdiosAmigos', 5);Code language: SQL (Structured Query Language) (sql)Output:
6. LENGTH
The LENGTH() function is used to return the number of characters present in a string.
Example:
SELECT LENGTH('Amatures');Code language: SQL (Structured Query Language) (sql)Output:
7. LPAD
The LPAD() function is used to pad a string on the left to a certain length with another string.
Example:
SELECT LPAD('Amigos', 10, '*');Code language: SQL (Structured Query Language) (sql)Output:
AlsR read MySQL LPAD() and RPAD() – Add Arbitrary Characters to a String
8. MD5
The MD5() function is used to calculate the MD5 hash of a string and returns the result in hexadecimal.
Example:
SELECT MD5('Amigos');Code language: SQL (Structured Query Language) (sql)Output:
9. POSITION
The POSITION() function is used to return the first occurrence of a substring in a string.
Example:
SELECT POSITION('gos' IN 'Amigos');Code language: SQL (Structured Query Language) (sql)Output:
10. REGEXP_MATCHES
The REGEXP_MATCHES() function is used to search a pattern of regular expression in a string which returns the matched substrings.
Example:
SELECT REGEXP_MATCHES('400-800', '\d+');Code language: SQL (Structured Query Language) (sql)Output:
11. REGEXP_REPLACE
The REGEXP_REPLACE() function is used to replace a substring that matches a regular expression pattern with a replacement string.
Example:
SELECT REGEXP_REPLACE('Hola', 'Ho', 'Hell');Code language: SQL (Structured Query Language) (sql)Output:
12. RIGHT
The RIGHT() function is used to extract a specified number of characters from the end of the string.
Example:
SELECT RIGHT('PostgreSQL', 3);Code language: SQL (Structured Query Language) (sql)Output:
13. REPLACE
The REPLACE() function is used to replace all the occurrences of a target substring with a replacement string.
Example:
SELECT REPLACE('Hola', 'Ho', 'Hell');Code language: SQL (Structured Query Language) (sql)Output:
Also Read: MySQL REPLACE() – Edit Strings and Replace Values in MySQL
14. SPLIT_PART
The SPLIT_PART() function is used to split a string at each occurrence of a specified delimiter which returns the part at a given field number.
Example:
SELECT SPLIT_PART('SQLCODE', 'L', 3);Code language: SQL (Structured Query Language) (sql)Output:
15. SUBSTRING
The SUBSTRING() function is used to extract a substring from a string based on position and length.
Example:
SELECT SUBSTRING('SQLCODE', 4,4);Code language: SQL (Structured Query Language) (sql)Output:
16. TRANSLATE
The TRANSLATE() is used to replace each character in a string with the corresponding character in the translation mapping.
Example:
SELECT TRANSLATE('972139', '972', 'abc');Code language: SQL (Structured Query Language) (sql)Output:
17. TRIM
The TRIM() function is used to remove the longest string which contains only the characters or the spaces from the beginning, end or both ends of a string.
Example:
SELECT TRIM(' SQL ');Code language: SQL (Structured Query Language) (sql)Output:
18. TO_CHAR
The TO_CHAR() function is used to convert a date or number to a string according to a specified format.
Example:
SELECT TO_CHAR(TO_DATE('210623', 'YYMMDD'), 'YY/MM/DD');Code language: SQL (Structured Query Language) (sql)Output:
19. TO_NUMBER
The TO_NUMBER() function is used to convert a given string to a number according to a specified format.
Example:
SELECT TO_NUMBER(REPLACE('79,56.66', ',', '.'), '99.99');Code language: SQL (Structured Query Language) (sql)Output:
Conclusion
The PostgreSQL String function plays a crucial role in data management and data manipulation, allowing users to efficiently handle textual data and perform a wide range of operations to meet their application requirements. In this tutorial, we have provided you with many such functions. It contributes to improving the data quality and consistency. We hope you enjoyed the tutorial.