w3resource logo


postgresql String Function part3

PostgreSQL String Functions and Operators - Part-III

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

<<PreviousNext>>

Description

See : PostgreSQL string functions Part - I. , PostgreSQL string functions Part - II

Here is the list of 10 string functions bellow.

List of 10 String Functions

Functions Syntax Example Output
repeat() repeat(<string>,<repeating_number>) repeat('*--*', 2); *--**--*
replace() replace(<string>,<matching_string>,<replace_with>) replace('test string', 'st', '**'); te** **ring
rpad() rpad(<string>, length, [<fill_text >]) rpad('w3r', 10, 'esource'); w3resource
rtrim() rtrim(<string> [, < trimming_text >]) rtrim('rtrimtest', 'best'); rtrim
split_part() split_part(<string>,<delimiter>, <field_number>) split_part('ordno-#-orddt-#-ordamt', '-#-', 2); orddt
strpos() strpos(<string>, < substring >) strpos('w3resource', 'so'); 5
substr() substr(<string>,<position_from >
[,<number_of_characters>])
substr('w3resource',2,3); 3re
translate() translate(<string>,<matching_string>,
<replace_string>])
translate('translate', 'rnlt', '123'); 1a2s3ae
right() right(<string>,n) right('w3resource',5); ource
reverse() reverse(<string>) reverse('w3resource'); ecruoser3w

The sample table

postgresql sample table employee example1

Example of PostgreSQL repeat() function

The PostgreSQL repeat function is used to repeat a specified string to a specified number of times

SQL

SELECT repeat('test__', 5),repeat('*--*', 5);              

Output

PostgreSQL repeat function example1

Explanation

In the above example, the specified strings have repeated specified number of times.

Example of PostgreSQL replace() function

The PostgreSQL replace function is used to replace all occurrences of matching_string in the string with the replace_with string.

SQL

SELECT replace('test string', 'st', '**');              

Output

PostgreSQL replace function example1

Explanation

In the above example, the specified string 'st' have been replaced by '**' in the 1st parameter within the argument.

Example of PostgreSQL replace() function using column

If we want to display the empno, designame and the newly formatted designame column which is including '***' instead of 'ER' from employee table for those employees, who drawn a salary of more than 15000 , the following sql can be used.

SQL

SELECT empno,designame, replace(designame,'ER','***')FROM employee WHERE salary>15000;              

Output

PostgreSQL replace function example2

Explanation

In the above example, the 'ER' substring within the designame column have been replaced by '***'.

Example of PostgreSQL rpad() function

The PostgreSQL rpad 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 left..

SQL

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

PostgreSQL rpad function example1

Explanation

In the above example, the 1st parameter within the argument is string i.e. 'w3r', the second one is length, i.e. 10 and the third one is fill_text, i.e. 'esource'. Therefore string have been right padded upto the specific length i.e. 10 by the fill_text i.e. 'esource' and displayed the above output.

PostgreSQL rpad() function example - 2

SQL

SELECT rpad('w3r', 7, 'esource');              

PostgreSQL rpad function example2

Explanation

In the above example, the 1st parameter within the argument is string i.e. 'w3r', the second one is length, i.e. 7 and the third one is fill_text, i.e. 'esource'. Therefore string have been right padded upto the specific length i.e. 7 by the fill_text i.e. 'esource' and displayed the above output. The point to be noted that, the total length of string and fill_text is larger than the length as specified.

PostgreSQL rpad() function example - 3

SQL

SELECT rpad('w3r', 14, 'esource');              

PostgreSQL rpad function example3

Explanation

In the above example, the 1st parameter within the argument is string i.e. 'w3r', the second one is length, i.e. 14 and the third one is fill_text, i.e. 'esource'. Therefore string have been right padded upto the specific length i.e. 14 by the fill_text i.e. 'esource' and displayed the above output. The point to be noted that, the total length of string and fill_text is smaller than the length as specified.So, the fill_text right padded the string until the total length is 14.

Example of PostgreSQL rtrim() function

The PostgreSQL rtrim 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 end of a string.

SQL

SELECT rtrim('rtrimtest', 'best');               

Output

PostgreSQL rtrim function example1

Explanation

In the above example, the matching text is 'best' in the second parameter in which 'e','s' and 't' is matching from the end of the first parameter and removed from the string.

PostgreSQL rtrim() function example - 2

SQL

SELECT rtrim('rtrim    ');               

Output

PostgreSQL rtrim function example2

Explanation

In the above example, no matching text is specified so the default white spaces have been removed from the end of the string.

Example of PostgreSQL split_part() function

The PostgreSQL split_part function is used to split a given string based on delimiter and pick out the desire field from the string, start from left of the string.

SQL

SELECT split_part('ordno-#-orddt-#-ordamt', '-#-', 2);              

Output

PostgreSQL split_part function example1

Explanation

In the above example, the delimiter of the defined string is '-#-' and specified field number is 2. So the split_part function splits the second field from the specified string.

Example of PostgreSQL strpos() function

The PostgreSQL length function is used to find the position, from where the substring is being matched within the string.

SQL

SELECT strpos('w3resource', 'so')AS "Position of substring";              

Output

PostgreSQL strpos function example1

Explanation

In the above example, the position of the specified substring 'so' within the string as specified in the first parameter within the argument is 5.

Example of PostgreSQL strpos() function using column

If we want to display the empno, designame and the position of a specific substring 'ER', which must be exists within the column designame from employee table, the following sql can be used.

SQL

SELECT empno,designame, strpos(designame,'ER') AS "Position of 'ER' "FROM employee WHERE strpos(designame,'ER')>0;              

Output

PostgreSQL strpos function example2

Example of PostgreSQL substr() function

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

SQL

SELECT substr('w3resource',2,3) AS "Extracting characters";              

PostgreSQL substr function example1

Explanation

In the above example, three characters from the second position of the string 'w3resource' have been extracted.

PostgreSQL substr() function example - 2

If we want to display the emp_first_name, designame, and the extraction of three characters from the second position of designame column from employee table for those employees who drawn the salary of more than 18000, the following sql can be used.

SQL

SELECT emp_first_name,designame, substr(emp_first_name,2,3) AS "Extracting characters"FROM employee WHERE salary>18000;              

PostgreSQL substr function example2

Example of PostgreSQL translate() function

The PostgreSQL translate function is used to translate any character in the string by a character in replace_string. The characters in replace_string is corresponding to the characters in matching_string. The translate will happen when any character in the string matching with the character in the matching_string.

SQL

SELECT translate('translate', 'rnlt', '123');              

PostgreSQL translate function example1

Explanation

In the above example, the each character in the first parameter is searching in the second parameter and replacing by the substitute character from the third parameter. Here 't' found in the fourth place in the second parameter and no substitute character defined for this place, so no translate will take place. The second character 'r' is matching with first character of 'rnlt' and translate with '1'. Thus 'a' is not matching and no translate have done, 'n' is matching and translate with '2', 's' is not matching and no translate have done, 'l' is matching and translate with '3', 'a' is not matching and no translate have done, 't' is matching but no substitute character defined, so no translate will take place, 'e' is not matching and no translate have done.

Example of PostgreSQL right() function

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

SQL

SELECT right('w3resource',5);              

PostgreSQL right function example1

Explanation

In the above example, the right most five characters from the string 'w3resource' have been extracted.

Example of PostgreSQL reverse() function

The PostgreSQL reverse function is used to arrange a string in reverse order.

SQL

SELECT reverse('w3resource');              

PostgreSQL reverse function example2

Explanation

In the above example, the string 'w3resource' have arranged in reverse order.



<<PreviousNext>>