Once the art of writing Regex patterns is learned and mastered, playing with the database will be much more fun. The inclusion of regex patterns adds more power to it. PostgreSQL, with all its built-in functions, makes it a powerful system. SELECT FirstName, SUBSTRING(FirstName from '.$') as sub1, substring(FirstName from '%#"o_a#"_%' for '#') as sub2 from Person It is recommended to get hold of Regex patterns before implementing them haphazardly. Returns a substring from string str that matches the POSIX Regex or SQL Regex. SUBSTRING(str from posix_pattern) / SUBSTRING(str from sql_pattern for escape) SELECT FirstName, SUBSTRING(FirstName from 2 for 4) as a sub from PersonĢ2. Returns a substring from string str starting at position pos of length len. SELECT Address, REGEXP_SPLIT_TO_TABLE(Address, E'\\s+') from Person Splits the string str into a table of substrings separated by POSIX Regex pattern. SELECT Address, REGEXP_SPLIT_TO_ARRAY(Address, E'\\s+') from Person Pattern E’\\s+’ means one or more blank spaces. Splits the string str into an array of substrings separated by POSIX Regex pattern. SELECT Address, REGEXP_MATCHES(Address, '.'), REGEXP_REPLACE(Address, '.', 'Street') from Person Replaces all substrings that match the POSIX Regex pattern with the newstr. SELECT Address, REGEXP_MATCHES(Address, '.i.') from Perso Returns all substrings that match the POSIX Regex pattern. SELECT FirstName, REVERSE(FirstName) from Person SELECT Address, REPLACE(Address, 's', 'SS') from Person Replaces all occurrences of sub-string from_str with sub-string to_str in the string str.
SELECT Address, QUOTE_IDENT(Address), QUOTE_LITERAL(Address) from Person This query quotes and un-quotes the string str. SELECT Address, POSITION('Avenue' in Address) from Personġ4. 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. However, it is unlike the operation of the Length function in SQL. 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. SELECT Id || FirstName || LastName || phone || address as Concat_All from PersonĬapitalizes the string, i.e., each word’s first letter is upper-cased, and the rest are lower-cased. 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ģ. Returns the length of the string str in bits.
0 kommentar(er)
