w3resource logo


postgresql String Function part1

PostgreSQL String Functions and Operators - Part-I

rating PostgreSQL String Functions and Operators PartI has average rating 9 out of 10. Total 2 users rated.

<<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 below.

String Operator

Operator Syntax Example Output
| | ( concatenate operator ) string1 || string2 or sting || non-string or non-string || string 'w'||3||'resource' w3resource

List of 10 String Functions

Functions Syntax Example Output
bit_length() bit_length(<string>) bit_length('w3resource') 80
char_length or character_length () char_length(<string>) or character_length(<string>) char_length('w3resource') 10
lower() lower(<string>) lower('W3RESOURCE') w3resource
octet_length() octet_length(<string>) octet_length('w3resource') 10
overlay() overlay(<main_string> placing <replacing_string> from <starting_position>
[ for <mumber_of_characters> ])
overlay('w3333333rce' placing 'resou' from 3 for 4) w3resou33rce
position() position('search_string' in 'main_string') position('our' in 'w3resource') 6
substring() substring(<main_string> from <extracting_position> for <extracting_characters> ) SELECT substring('w3resource' from 4 for 5) esour
trim() trim([leading|trailing|both] <removing_string> from <main_string> ) trim(' w3resource ') w3resource
upper() upper(<string>) upper('w3resource') W3RESOURCE
ascii() ascii(<string>) ascii('w3resource') 119

The sample table

postgresql sample table employee example1

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

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 employeeWHERE deptno=15;              

Output

PostgreSQL concatenate operator example2

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 bit_length function example1

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 example1

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 employeeWHERE deptno=15;              

Output

PostgreSQL char_length function example2

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 lower function example1

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 octet_length function example1

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

Parameters Description
main_string The string on which function will work.
replacing_string The string which will be replaced.
starting_position The position from where the replacement will be start.
mumber_of_characters The replacement string containing the number of characters. It is optional.

Example of PostgreSQL overlay() function

SQL

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

Output

PostgreSQL overlay function example1

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

PostgreSQL overlay function example2

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

PostgreSQL overlay function example3

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

PostgreSQL overlay function example4

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

PostgreSQL position function example1

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);              

PostgreSQL substring function example1

Explanation

In the above example shows, five characters from the fourth 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

Parameters Description
leading | trailing | both The position of the main_string from where the removing_string will be removed.
removing_string The string wihch will be removed. It is optional.
main_string The main string.

Example of PostgreSQL trim() function

SQL

SELECT trim(from '   w3resource   ');              

PostgreSQL trim function example1

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');              

PostgreSQL trim function example2

Explanation

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

SQL

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

PostgreSQL trim function example3

Explanation

In the above example shows, the leading characters 'te' have removed from the given string.

PostgreSQL upper() function

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

SQL

SELECT upper('w3resource');              

PostgreSQL upper function example1

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";              

PostgreSQL ascii function example1



<<PreviousNext>>