PostgreSQL String Functions

In today’s article, we will discuss the most used textual functions for String operations in PostgreSQL.

ASCII(): Returns numeric value of left-most character

select ASCII(‘faRUK’)

1	select ASCII(‘faRUK’)

BIT_LENGTH(): Returns length of argument in bits

select BIT_LENGTH(‘faRUK’)

1	select BIT_LENGTH(‘faRUK’)

CHAR_LENGTH(): Returns number of characters in argument

select CHAR_LENGTH(‘FARUK’)

1	select CHAR_LENGTH(‘FARUK’)

CHARACTER_LENGTH(): A synonym for CHAR_LENGTH()

CONCAT_WS(): Returns concatenate with separator

select  CONCAT_WS(‘,’,’FARUK ‘,’ ERDEM’)

1	select CONCAT_WS(‘,’,’FARUK ‘,’ ERDEM’)

CONCAT(): Returns concatenated string

select  CONCAT(‘FARUK ‘,’ ERDEM’)

1	select CONCAT(‘FARUK ‘,’ ERDEM’)

LEFT(): Returns the leftmost number of characters as specified

select LEFT(‘FARUK’,2)

1	select LEFT(‘FARUK’,2)

LENGTH(): Returns the length of a string in bytes

select LENGTH(‘FARUK ERDEM’)

1	select LENGTH(‘FARUK ERDEM’)

LOWER(): Returns the argument in lowercase

select lower(‘FARUK’)

1	select lower(‘FARUK’)

LTRIM(): Removes leading spaces

select LTRIM(‘        FARUK’)

1	select LTRIM(‘ FARUK’)

REPEAT(): Repeats a string the specified number of times

select REPEAT(‘PostgreSQL’,2)

1	select REPEAT(‘PostgreSQL’,2)

REPLACE(): Replaces occurrences of a specified string

SELECT REPLACE(‘www.farukerdm.com’, ‘farukerdm’, ‘farukerdem’);

1	SELECT REPLACE(‘www.farukerdm.com’, ‘farukerdm’, ‘farukerdem’);

REVERSE(): Reverse the characters in a string

select reverse(‘abcd’)

1	select reverse(‘abcd’)

RIGHT(): Returns the specified rightmost number of characters

SELECT RIGHT(‘farukerdem’, 5);

1	SELECT RIGHT(‘farukerdem’, 5);

RTRIM(): Removes trailing spaces

SELECT RTRIM(‘faruk   ‘);

1	SELECT RTRIM(‘faruk ‘);

SUBSTRING(), SUBSTR(): Returns the substring as specified

select SUBSTRING(‘FARUKERDEM’,6,10) AS "6th Character and After"

1	select SUBSTRING(‘FARUKERDEM’,6,10) AS "6th Character and After"

TRIM(): Removes leading and trailing spaces

select trim(‘  faruk ‘)

1	select trim(‘ faruk ‘)

UPPER(): Converts to uppercase

SELECT UPPER(‘faruk’);

1	SELECT UPPER(‘faruk’);

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux