w3resource logo


postgresql String Function part2

PostgreSQL String Functions and Operators - Part-II

rating 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

Functions Syntax Example Output
btrim() btrim(<string>[ , <text_trim>]) btrim('settw3resourcesttes', 'test') w3resourc
chr() chr(<int>) chr(90) Z
concat() concat(<string1>,[<string2>,<string3>,...]) concat('w',3,'r', 'esource','.','com'); w3resource.com
initcap() initcap(<string>) initcap('RABINDRANATH TAGORE') Rabindranath Tagore
left() left(<string>,<no_of_characters>) left('w3resource',3) w3r
length() length(<string>) length('w3resource') 10
lpad() lpad(<string>,<length>[,<fill_text>]) lpad('esource', 10, 'w3r') w3resourc
ltrim() ltrim(<string>[, <triming_text>]) ltrim('testltrim', 'best'); ltrim
pg_client_
encoding()
pg_client_encoding
(string)
pg_client_encoding() UTF8
quote_ident() quote_ident(<string>) quote_ident('SELECT * FROM employee') "SELECT * FROM employee"

The sample table

postgresql sample table employee example1

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

PostgreSQL btrim function example1

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

PostgreSQL chr function example2

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

PostgreSQL concat function example1

Example of PostgreSQL concat() function with NULL

SQL

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

PostgreSQL concat function example2

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

PostgreSQL initcap function example1

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

PostgreSQL initcap function example2

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

PostgreSQL left function example1

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

PostgreSQL left function example2

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

PostgreSQL length function example1

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

PostgreSQL length function example2

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

PostgreSQL lpad function example1

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

PostgreSQL lpad function example2

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

PostgreSQL lpad function example3

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

PostgreSQL ltrim function example2

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

PostgreSQL ltrim function example2

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

PostgreSQL pg_client_encoding function example1

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

PostgreSQL quote_ident function example1



<<PreviousNext>>