PostgreSQL String Functions

PostgreSQL String Functions for examining and manipulating string values. Strings in this context include values of the types character, character varying, and text.

PostgreSQL Date Functions with Examples:

1. PostgreSQL bit_length() function: It gives number of bits in string.

PostgreSQL bit_length() Examples:

select bit_length('r2schools');

 bit_length
------------
         72

2. PostgreSQL char_length() or character_length() function:It calculates the number of characters in a string.

PostgreSQL char_length() or character_length() Examples:

select character_length('r2schools');

3. PostgreSQL lower() function: This function converts string to lower case.

PostgreSQL lower() Example:

select lower('R2SCHOOLS');

4. PostgreSQL upper() function: This function converts string to upper case.

PostgreSQL upper() Example:

select upper('r2schools');

5. PostgreSQL position() function: This function finds the location of specified substring.

PostgreSQL position() Example:

select position('r2' in 'welcome to r2schools');

6. PostgreSQL ascii() function: ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character.

PostgreSQL ascii() Example:

select ascii('r2schools');

7. PostgreSQL concat() function: concat function is used join the strings. This is simply concatenation operation.

PostgreSQL ascii() Example:

select concat('Welcome to ','r2schools');

8. PostgreSQL trim() function:trim() removes the longest string containing only the characters from the start/end/both ends of the string.

PostgreSQL trim() Example:

select trim('          Welcome to r2schools ');

9. PostgreSQL trim() function:ltrim() removes the longest string containing only characters from characters from the start of string.

PostgreSQL ltrim() Example:

select ltrim('          Welcome to r2schools    ');

10. PostgreSQL rtrim() function:rtrim() removes the longest string containing only characters from characters from the end of string.

PostgreSQL rtrim() Example:

select rtrim('          Welcome to r2schools    ');

11. PostgreSQL initcap() function:initcap() converts the first letter of each word to upper case and the rest to lower case.

PostgreSQL inticap() Example:

select initcap('welcome to r2schools');

12. PostgreSQL length() function:length() function gives number of characters in a string.

PostgreSQL length() Example:

 select length('welcome to r2schools');

13. PostgreSQL lpad() function:lpad() fills up the string to length length by prepending the characters fill. If the string is already longer than length then it is truncated (on the right).

PostgreSQL lpad() Example:

 select lpad('Welcome to r2schools',25,'Pg');

14. PostgreSQL rpad() function:rpad() fills up the string to length length by appending the characters fill. If the string is already longer than length then it is truncated.

PostgreSQL rpad() Example:

 select rpad('Welcome to r2schools',25,'Pg');

15. PostgreSQL reverse() function:reverse() gives the entered string in reverse.

PostgreSQL reverse() Example:

  select reverse('R2schools');

16. PostgreSQL replace() function:replace() replaces all occurrences in string of substring from with substring to

PostgreSQL replace() Example:
Following example replaces cd with xx.

  select replace('abcdefcdghxxifjk','cd','xx');

17. PostgreSQL repeat() function:repeat() repeats a string specified times.

PostgreSQL repeat() Example:
Following example repeats ‘PostgreSQL’ two times.

select repeat('PostgreSQL',2);

18. PostgreSQL substr() function:substr() extracts string from the specified position to number of strings.

PostgreSQL substr() Example:
Following example takes string from position 8 to 3 characters.

select substr('PostgreSQL is open source db',8,3);

19. PostgreSQL strpos() function:strpos() finds the location of specified sub-string.

PostgreSQL strpos() Example:

select strpos('PostgreSQL is open source db','is');

So in this article, we have covered frequently used PostgreSQL String Functions. Still some are there.