PostgreSQL - String Functions and Operators slides presentation

Transcript

PostgreSQL String Functions and Operators

PostgreSQL concatenate operator

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

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

Output : w3resource

PostgreSQL bit_length() function

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

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

Output : 80

char_length(),character_length()

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

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

Output : 10

PostgreSQL lower() function

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

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

Output : w3resource

PostgreSQL octet_length() function

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

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

Output : 10

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.

Syntax : overlay( placing from [ for ] )

The replacement substring mentioned by the starting_position, from where the replacement substring will start and number_of_characters mentioned for replacement from the specified position.

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

Output : w3resou3rce

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

Output : w3resou33rce

PostgreSQL position() function

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

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

Output : 6

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.

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

Output : esour

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.

Example : SELECT trim(from ' w3resource ');

Output : w3resource

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

Output : tetew3resource

PostgreSQL upper() function

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

Example : SELECT upper('w3resource');

Output : W3RESOURCE

PostgreSQL ASCII() function

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

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

Output : 119

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.

Example : SELECT btrim('settw3resourcesttes', 'test');

Output : w3resourc

PostgreSQL chr() function

The PostgreSQL chr function is used to return the corresponding character against the given code within the argument.

Example : SELECT chr(90) AS "CODE of chr(90)";

Output : Z

PostgreSQL concat() function

The PostgreSQL concat function is used to concatenate all arguments except NULL, it will be ignored.

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

Output : w3resource.com

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

Output : w3resource.com

PostgreSQL initcap() function

The PostgreSQL initcap function is used to convert the first letter of each word to uppercase and the remaining to lower case.

Example : SELECT initcap('RABINDRANATH TAGORE')

AS "First Character OR each word Capital";

Output : Rabindranath Tagore

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.

Example : SELECT left('w3resource',3)

AS "Extract 3 characters from the left";

Output : w3r

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.

Example : SELECT length('w3resource')

AS "Length of a String";

Output : 10

PostgreSQL lpad() function

The PostgreSQL lpad function is used to fill up a string of specific length by a substring. If the substring length is equal to the remaining main string length, it will fill up properly, but if less, 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.

Example : SELECT lpad('esource', 10, 'w3r');

Output : w3resource

Example2 : SELECT lpad('esource', 13, 'w3r');

Output : w3rw3resource

Example3 : SELECT lpad('w3esource', 8, 'lpad');

Output : w3resour

PostgreSQL ltrim() function

The PostgreSQL ltrim function is used to remove spaces or set of characters which are matching with the trimming_text, from the start of a string.

Example1 : SELECT ltrim('testltrim', 'best');

Output : ltrim

Example2 : SELECT ltrim(' ltrim');

Output : ltrim

PostgreSQL pg_client_encoding() function

The PostgreSQL pg_client_encoding function is used to get the current client encoding name.

Example : SELECT pg_client_encoding();

Output : UTF8

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.

Example : SELECT quote_ident('SELECT * FROM employee');

Output : “ SELECT * FROM employee”

PostgreSQL repeat() function

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

Example : SELECT repeat('test__', 5);

Output : test_test_test_test_test_

PostgreSQL replace() function

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

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

Output : te** **ring

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.

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

Output : w3resource

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

Output : w3resou

PostgreSQL rtrim() function

The PostgreSQL rtrim function is used to remove spaces or set of characters which are matching with the trimming_text, from the end of a string.

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

Output : rtrim

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.

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

Output : orddt

PostgreSQL strpos() function

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

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

Output : 5

PostgreSQL substr() function

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

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

Output : 3re

PostgreSQL translate() function

The PostgreSQL translate function is used to translate any character in the string by a character in the specified replace string, 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.

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

Output : 1a2s3ae

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.

Example : SELECT right('w3resource',5);

Output : ource

PostgreSQL reverse() function

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

Example : SELECT reverse('w3resource');

Output : ecruoser3w