PostgreSQL String Functions and Operators - Part-I

<<PreviousNext>>

Description

The PostgreSQL string functions can be used for manipulating and examining various type of queries independently or within a table. Here is the list of operator and 10 string functions bellow.

String Operator

List of 10 String Functions

The sample table

PostgreSQL concatenate operator ( | | )

The PostgreSQL concatenate operator ( || ) is used to concatenate two or more strings and non strings.

SQL

SELECT 'w'||3||'resource' AS "Concatenate Operator ( || )";
              

Output

PostgreSQL concatenate operator ( | | ) using Column Name

If we want to display the first name, last name and concatenate of first name and last name of those employee who belongs to the dept 15 from the employee table, the following SQL can be executed:

SQL

SELECT emp_first_name,emp_last_name,
emp_first_name||' '||emp_last_name 
AS "Name of Employee" FROM employee
WHERE deptno=15;
              

Output

PostgreSQL bit_length() function

The PostgreSQL bit_length function is used to count the number of bits in a specified string.

SQL

SELECT bit_length('w3resource') AS "bit_length";
              

Output

PostgreSQL char_length() or character_length() function

The PostgreSQL char_length function or character_length function is used to count the number of characters in a specified string.

SQL

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

OR

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

Output

PostgreSQL char_length function using Column

If we want to display the first name, last name and concatenate of first name and last name and number of character after concatenation of those employee who belongs to the dept 15 from the employee table, the following SQL can be executed:

SQL

SELECT emp_first_name,emp_last_name,
emp_first_name||' '||emp_last_name 
AS "Name of Employee",
char_length(emp_first_name||' '||emp_last_name )
AS "Length of Name" 
FROM employee
WHERE deptno=15;
              

Output

PostgreSQL lower() function

The PostgreSQL lower function is used to convert a string from upper case to lower case.

SQL

SELECT lower('W3RESOURCE') AS "Upper to Lower";
              

PostgreSQL octet_length() function

The PostgreSQL octet_length function is used to count the number of bytes in a specified string.

SQL

SELECT octet_length('w3resource') AS "octet_length";
              

Output

PostgreSQL overlay() function

The PostgreSQL overlay function is used to replace a specified text or string in place of a text or substring within a mother string. The replacement substring mentioned by the position, from where the replacement substring will start and number of characters mentioned for replacement from the specified position.

Syntax

overlay(<main_string> placing <replacing_string> from <starting_position> [ for <mumber_of_characters>] )

Parameters

Example of PostgreSQL overlay() function

SQL

SELECT overlay('w3333333rce' placing 'resou' from 3)
              

Output

Explanation

In the above example the string 'resou' replaces 5 consecutive characters of the string 'w3333333rce', starting from the third character form the left. Thus we get the result "w3resou3rce". Notice that, since we have not mentioned the second parameter, i.e. how many characters to be replaced, it replaces the number of characters equal to the number of characters present in the replacing_string, which is five.

SQL

SELECT overlay('w3333333rce' placing 'resou' from 3 for 4)
              

Output

Explanation

In the above example four consecutive characters from the third position have been replaced by the string 'resou'. Thus we get the result "w3resou33rce". Notice that, here we have mentioned the second parameter, i.e. how many characters to be replaced, it is four, so it replaces four characters from the third position of the main_string.

SQL

SELECT overlay('w333333rce' placing 'resou' from 3 for 5);
              

Output

Explanation

In the above example five consecutive characters from the third position have been replaced by the string 'resou'. Thus we get the result "w3resource". Notice that, here we have mentioned the second parameter, i.e. how many characters to be replaced, it is five, so it replaces five characters from the third position of the main_string.

SQL

SELECT overlay('w3333333rce' placing 'resou' from 3 for 6);
              

Output

Explanation

In the above example six consecutive characters from the third position have been replaced by the string 'resou' which containing five characters. Thus we get the result "w3resouce". Notice that, here we have mentioned the second parameter, i.e. how many characters to be replaced, it is six, it is more than the replacing_string, so it replaces six characters from the third position of the main_string.

PostgreSQL position() function

The PostgreSQL position function is used to find the location of substring within a specified string.

SQL

SELECT position('our' in 'w3resource');
              

Output

Explanation

In the above example the substring 'our' present at sixth position in the string 'w3resource'.

PostgreSQL substring() function

The PostgreSQL substring function is used to extract a string containing a specific number of characters from a particular position of a given string.

SQL

SELECT substring('w3resource' from 4 for 5);
              

Explanation

In the above example shows, five characters from the forth position i.e. 'esour' have extracted form the given string 'w3resource'.

PostgreSQL trim() function

The PostgreSQL trim function is used to remove spaces or set of characters from the leading or trailing or both side from a string.

Syntax

trim([leading|trailing|both] [removing_string] from <main_string> )

Parameters

Example of PostgreSQL trim() function

SQL

SELECT trim(from '   w3resource   ');
              

Explanation

In the above example the leading and trailing spaces have removed from the given string and gets result 'w3resource'. Notice that, here we have not mentioned the removing position and removing string, so by default trim function removes white spaces from both the side of the string.

SQL

SELECT trim(trailing 'st' from 'tetew3resourcestst');
              

Explanation

In the above example shows, the trailing characters 'st' have removed from the given string.

SQL

SELECT trim(leading 'te' from 'tetew3resourcestst');
              

Explanation

PostgreSQL upper() function

The PostgreSQL upper function is used to convert a string from lower case to upper case.

SQL

SELECT upper('w3resource');
              

PostgreSQL ASCII() function

The PostgreSQL ASCII function is used to get the code of the first character of a given string.

SQL

SELECT ascii('w3resource') AS "ASCII of first character";