Remember, the index starts from 1 in PostgreSQL.

POSITION(substr in str) / STRPOS(str, substr)įinds the position of the substring substr in the string str. SELECT LTRIM(' abc ') as L1, RTRIM(' abc ') as R1, TRIM(' abc ') as T1, LTRIM('xxxyyabcxyz', 'xyz') as L2, RTRIM('xxxyyabcxyz', 'xyz') as R2, TRIM('xxxyyabcxyz', 'xyz') as T2ġ3. If chars are not specified in the arguments, spaces are trimmed. Returns the string str after trimming all char(s) occurrences from left, right or both ends. LTRIM(str, chars) / RTRIM(str, chars) / TRIM(str, chars) SELECT FirstName, LastName, LPAD(CONCAT_WS(' ', FirstName, LastName), CHAR_LENGTH(CONCAT_WS(' ', FirstName, LastName))+CHAR_LENGTH('Mr. Inserts sub-string from position 0 of the string padstr at the beginning and end of the string str until the resultant string is of len characters. LPAD(str, len, padstr) / RPAD(str, len, padstr) This happens because Euro (€) sign occupies 3 bytes in memory. SELECT '€' as multibyte_char, LENGTH('€'), CHAR_LENGTH('€'), OCTET_LENGTH('€') The difference comes when there are multibyte characters involved. This is very much similar to LENGTH and CHAR_LENGTH functions. SELECT FirstName, LENGTH(FirstName), CHAR_LENGTH(FirstName), OCTET_LENGTH(FirstName) from Person SELECT FirstName, LENGTH(FirstName), CHAR_LENGTH(FirstName) from PersonĬalculates the length of the string str in bytes. When specified, encoding provides the length in the particular encoding. This is unlike the operation of the Length function in the SQL, though. Returns the length of the string str in characters. SELECT FirstName, LastName, CONCAT(LEFT(LastName, 3), RIGHT(FirstName, 2)) as LoginID from Person When len is negative, it returns the string str except for the leftmost or rightmost len characters. Returns the leftmost and rightmost len characters from the string str. SELECT FirstName, LOWER(FirstName) as Lower, UPPER(FirstName) as Upper from Person Select INITCAP('This is a PostgreSQL example.')Ĭonverts a string to lower case and upper case. Non-alphanumeric separators determine words. each word’s first letter is upper-cased, and the rest are lower-cased. SELECT Id || FirstName || LastName || phone || address as Concat_All from PersonĬapitalizes the string, i.e. SELECT FirstName, LastName, CONCAT(FirstName, LastName) as DisplayName from PersonĬoncatenates str1, str2 to strn and even non-string arguments. Returns a string formed by joining str1 to strn. SELECT FirstName, CHAR_LENGTH(FirstName) from Person SELECT FirstName, BIT_LENGTH(FirstName) from Personģ.

Postgresql concat string