PostgreSQL String Functions and Operators - Part-II

has average rating 6 out of 10. Total 5 users rated.

<<PreviousNext>>

Description

See : PostgreSQL string functions.

Here is the list of 10 string functions bellow.

List of 10 String Functions

The sample table

Example of PostgreSQL btrim() function

The PostgreSQL btrim function is used to remove the longest string specified in the argument from the start and end of the given string. If no string for removing default space will be removed from leading and trailing side from the string.

SQL

SELECT btrim('settw3resourcesttes', 'test');
              

Output

Explanation

In the above example the btrim searches the letters 't' or 'e' or 's' or 't' from the left or right most position of the given string and removes those letters which matches consecutively and displays the result 'w3resourc'.

Example of PostgreSQL chr() function

The PostgreSQL chr function is used to return the corresponding character against the given code within the argument.

SQL

SELECT chr(90) AS "CODE of chr(90)";
              

Output

Example of PostgreSQL concat() function

The PostgreSQL concat function is used to concatenate all arguments except NULL, it will be ignored.

SQL

SELECT concat('w',3,'r', 'esource','.','com');
              

Example of PostgreSQL concat() function with NULL

SQL

SELECT concat('w',3,'r', 'esource',NULL,'.','com');
              

Explanation

In the above example the concat function ignore the NULL and displays the result.

Example of PostgreSQL initcap() function

The PostgreSQL initcap function is used to convert the first letter of each word to upper case and the remaining to lower case.

SQL

SELECT initcap('RABINDRANATH TAGORE') 
AS "First Character OR each word Capital";
              

Output

Example of PostgreSQL initcap() function using column with nested functions

SQL

If we want to display the emp_first_name, emp_last_name and concatenated name and the concatenated name in proper case of those employees who belongs to the designation 'CLERCK', the following sql statement can be used:

SELECT emp_first_name, emp_last_name,
concat(emp_first_name, emp_last_name)
AS "Name of Employee",
initcap(concat(emp_first_name, emp_last_name))
AS "Proper Name"
FROM EMPLOYEE
WHERE designame='CLERCK';
              

Output

Example of PostgreSQL left() function

The PostgreSQL left function is used to extract n number of  characters specified in the argument from the left of a given string. When the value of n is negative, the extraction will be the last n characters.

SQL

SELECT left('w3resource',3) 
AS "Extract 3 characters from the left";
              

Output

Explanation

In the above example the left function extracted 3 characters from the left of the string 'w3resource'

Example of PostgreSQL left() function using column

SQL

If we want to display the emp_first_name, emp_last_name and concatenated name of those employees who belongs to such a designation, which first 3 characters is 'CLE', the following sql statement can be used:

SELECT emp_first_name, emp_last_name,
concat(emp_first_name, emp_last_name)
AS "Name of Employee",designame
FROM EMPLOYEE
WHERE left(designame,3)='CLE';
              

Output

Example of PostgreSQL length() function

The PostgreSQL length function is used to find the length of a string i.e. number of characters in the given string.

SQL

SELECT length('w3resource')
AS "Length of a String";
              

Output

Example of PostgreSQL length() function using column

SQL

If we want to display the emp_first_name and the length of emp_first_name of those employees who maintain such criteria that the length of their first name must be more than 5, the following sql statement can be used:

SELECT emp_first_name,length(emp_first_name)
AS "Length of a First Name"
FROM employee 
WHERE length(emp_first_name)>5;
              

Output

Example of PostgreSQL lpad() function

The PostgreSQL lpad function is used to fill up a string of specific length by a substring. If the length of the substring is equal to the remaining length of main string it will fill up properly, if less than the remaining length, the substring will repeat until it is not filling up, if longer than the remaining length or specified length it will be truncated on the right.

SQL

SELECT lpad('esource', 10, 'w3r');
              

Explanation

In the above example the main string is 'esource' and the length is 7, the substring is 'w3r' of length 3 and the string have to be a length of 10. So, remaining length is 3, and the substring 'w3r' will fill up properly and the result is 'w3resource'.

Example 2 of PostgreSQL lpad() function

SQL

SELECT lpad('esource', 13, 'w3r');
              

Explanation

In the above example the main string is 'esource' and the length is 7, the substring is 'w3r' of length 3 and the string have to be a length of 13. So, remaining length is 6, and the substring 'w3r' will repeat two times to fill it up, thus the result is 'w3rw3resource'.

Example 3 of PostgreSQL lpad() function

SQL

SELECT lpad('w3resource', 8, 'lpad');
              

Explanation

In the above example the main string is 'w3resource' and the length is 10, the substring is 'lpad' of length 4 and the string have to be a length of 8. Here, the specified length is smaller than the string, so, instead of lpadding the string will be truncated by two characters from the right side of the string, thus the result is 'w3resour'.

Example of PostgreSQL ltrim() function

The PostgreSQL ltrim function is used to remove spaces( if no character(s) is provided as trimming_text ) or set of characters which are matching with the trimming_text, from the start of a string.

SQL

SELECT ltrim('testltrim', 'best');
              

Explanation

In the above example, the trimming_text is provided, and it is 'best'. From the result we can see that, the left four characters i.e. 'test' have removed and the characters 't' and 'e' and 's' are present in the trimming_text 'best'.

Example of PostgreSQL ltrim() function with no text

SQL

SELECT ltrim('     ltrim');
              

Explanation

In the above example, the trimming_text is not provided, so by default the white spaces form the left side of the given string will be removed.

Example of PostgreSQL pg_client_encoding() function

The PostgreSQL pg_client_encoding function is used to get the current client encoding name.

SQL

SELECT pg_client_encoding();
              

Example of PostgreSQL quote_ident() function

The PostgreSQL quote_ident function is used to make a given string with suitably double quoted, so as it can be used like an identifier in an sql statement string if required.

SQL

SELECT quote_ident('SELECT * FROM employee');