I l @ ve RuBoard |
PostgreSQL includes several functions that modify string- related data. These functions are particularly useful for controlling output displays and/or normalizing data input. ASCIIDescriptionThe ASCII function returns the ASCII value for the supplied character. Inputs ASCII( chr ) chr ”The character to determine the ASCII value of. ExamplesASCII('A') 65 ASCII('Apple') 65 NotesIn the case of multiple characters being supplied to the ASCII function, only the first is evaluated. CHRDescriptionThe CHR function returns the character that corresponds to the ASCII value provided. Inputs CHR( val ) val ”An ASCII value. ExampleCHR(65) 'A' INITCAPDescriptionThe INITCAP function forces a string or column to be returned when the first character is uppercase and the rest is lowercase only. Inputs INITCAP( col ) Or INITCAP( string ) ExampleSELECT INITCAP(name) AS Proper_Name FROM authors; Proper_Name -------- Bill Bob Sam LENGTH, CHAR_LENGTH, or CHARACTER_LENGTHDescriptionThe LENGTH ( or CHAR_LENGTH , or CHARACTER_LENGTH ) function returns the length of the supplied column. Inputs LENGTH( col ) col ”A column containing a string data type. ExampleSELECT name WHERE LENGTH(name)<4 FROM authors; Name ------ Pam Sam Sue Bob LOWERDescriptionThe LOWER function forces a string or column to be returned in lowercase only. Inputs LOWER( col ) Or LOWER( string ) ExampleSELECT LOWER(name) AS Low_Name FROM authors; Low_Name -------- bill bob sam LPADDescriptionThe LPAD function left-pads a string with specified characters or spaces. Inputs LPAD( str, len, fill ) str ”The string to left-pad. len ”The number of spaces to pad. fill ”By default, a space; however, any characters can be specified. ExamplesLPAD('Hello', 3) ' Hello' LPAD('ello', 3, 'H') 'HHHello' LTRIMDescriptionThe LTRIM function removes the specified characters from the left side of a character string. InputsLTRIM( str [, trim ]) str ”The string to trim. trim ”By default, a space; however, any character(s) can be specified. ExamplesLTRIM(' Hello') 'Hello' LTRIM('HHHello', 'H') 'ello' OCTET_LENGTHDescriptionThe OCTET_LENGTH function returns the length of a column or string, including any multibyte data present. Inputs OCTET_LENGTH( col ) Or OCTET_LENGTH( string ) ExampleSELECT OCTET_LENGTH('Hello World'); Octet_Length 11 NotesOCTET_LENGTH and LENGTH will often return the same value. However, a crucial difference is that OCTET_LENGTH is actually returning the number of bytes in a string. This can be an important difference if multibyte information is being stored. POSITIONDescriptionThe POSITION function returns an integer that represents the position of the supplied character string in the given column (or supplied string). InputsPOSITION( str IN col ) str ”The character string to locate. col ”The column or string to perform the search on. ExampleReturn the names from the table authors where the second letter is an 'a': SELECT name FROM authors WHERE POSITION('a' IN name))=2; Name ------ Pam Sam Tammy Barry STRPOSDescriptionThe STRPOS function returns an integer that represents the position of a specific character string in a given column (or supplied string). InputsSTRPOS( col , str ) col ”The column or string to perform the search on. str ”The character string to locate. ExampleSee the examples in the POSITION function section. NotesThis command is essentially the same as the POSITION function. RPADDescriptionThe RPAD function right-fills the specified string with spaces or characters. InputsRPAD( str, len [, fill ]) str ”The string to right-fill. len ”The number of spaces to append. fill ”By default, a space; however, any character can be used. ExamplesRPAD('Hello', 3) 'Hello ' RPAD('Hello', 3, '!') Hello!!! RTRIMDescriptionThe RTRIM function removes the specified characters from the right side of a character string. InputsRTRIM( str [, trim ]) str ”The string to right-trim. trim ”By default, a space; however, any character(s) can be used. ExamplesRTRIM('Hello ') 'Hello' RTRIM('Hello!!!', '!') 'Hello' SUBSTRINGDescriptionThe SUBSTRING function extracts a specified portion from an existing character string. InputsSUBSTRING( str FROM pos [ FOR len ]) str ”The string to manipulate. pos ”The starting position to begin extraction. len ”By default, the rest of the string is assumed; however, a specific portion can be specified. ExamplesSUBSTRING('Hello' FROM 2) 'ello' SUBSTRING('Hello' FROM 2 FOR 2) 'el' NotesThis is the same as the SUBSTR function. SUBSTRDescriptionThe SUBSTR function extracts a specified portion from an existing character string. InputsSUBSTRING( str, pos [, len ]) str ”The string to manipulate. pos ”The starting position to begin extraction. len ”By default, the rest of the string is assumed; however, a specific portion can be specified. ExamplesSUBSTRING('Hello', 2) 'ello' SUBSTRING('Hello', 2, 2) 'el' NotesThis is the same as the SUBSTRING function. TRANSLATEDescriptionThe TRANSLATE function performs a search and replace on a specified string. The data replaced is done according to where it matches in the search criteria. See the following example for more. Inputs TRANSLATE( str, searchset, replaceset ) str ”The base string to search and modify. searchset ”Either a single character or a multicharacter search set. replaceset ”Each respective member in this set replaces a corresponding member in the search set. ExamplesTRANSLATE('HelloW', 'W', '!') 'Hello!' TRANSLATE('Hello', 'Ho', 'Jy') 'Jelly' TRIMDescriptionThe TRIM function removes the specified character or whitespace from the left or right (or both) of a given string. InputsTRIM([ leading trailing both ] [ trim ] FROM str ) leading trailing both ” The side from which to remove the specified characters. trim ”By default, whitespace is assumed; however, any character(s) can be specified. str ”The string to trim. ExamplesTRIM(both FROM ' Hello ') 'Hello' TRIM(both '!' FROM '!!HELLO!!') 'Hello' UPPERDescriptionThe UPPER function forces a string or column to be returned in uppercase only. Inputs UPPER( col ) Or UPPER( string ) ExampleSELECT UPPER(name) AS Upper_Name FROM authors; Upper_Name -------- BILL BOB SAM |
I l @ ve RuBoard |