Oracle 11g Release 1 (11.1) function -- (returns the string value) character Function

Oracle 11g Release 1 (11.1) function -- (returns the value of a character) character function in this document content returns the value of a character function appendix ASCII Oracle character functions include: the character function that returns the character value. The NLS character function returns the numeric value. The character function returns the character value. The character function returns the following data types: if the input parameter is CHAR or VARCHAR2, the return value is VARCHAR2. If the input parameter is NCHAR or NVARCHAR2, the return value is NVARCHAR2. The length of the function return value is determined by the maximum length of the returned data type. For a function that returns CHAR or VARCHAR2, if the return value length exceeds the limit, the Oracle database intercepts and returns the value. No error is returned. For a function that returns CLOB, if the length of the returned value exceeds the limit, the Oracle database generates an error and returns nothing. Www.2cto.com CHR (n) CHR (n USING NCHAR_CS) returns VARCHAR2 characters equivalent to n binary in the database character set. The binary values (parameters and return values) are the same. If USING NCHAR_CS is specified, it is an international character set. For single-byte character sets, if n> 256, the Oracle database returns the n equivalent binary and 256 modulo. For multibyte character sets, n must resolve the entire Code Point. Invalid code points are not verified, and the results of specifying Invalid code points are uncertain. This function parameter is a NUMBER value, or any value that can be implicitly converted to a NUMBER, and returns a character. CONCAT (char1, char2) connection. Char1 and char2 can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The returned value is of the same type as the char1 Character Set and depends on the Data Type of the parameter. When two different data types are connected, the Oracle database does not return data after lossless conversion. Therefore, if one of the parameters is LOB, the return value is LOB. If one of the parameters is an international data type, the return value is also an international data type. For example: CONCAT (CLOB, NCLOB), return NCLOBCONCAT (NCLOB, NCHAR), return NCLOBCONCAT (NCLOB, CHAR), return NCLOBCONCAT (NCHAR, CLOB ), returns NCLOB. This function is equivalent to the concatenation operator (| ). INITCAP (char) LOWER (char) UPPER (char) NLS_INITCAP (char, 'nlsparam') NLS_LOWER (char, 'nlsparam') NLS_UPPER (char) the NLS_UPPER (char, 'nlsparam') INITCAP header is converted to a function that converts all characters in the UPPER-case LOWER to lowercase UPPER and starts with NLS _ *, which can be specified to be sensitive to specific languages.
Example 1: demonstrate language sensitivity. English is used to uppercase letters, but it is not necessary for other languages. SQL> SELECT NLS_INITCAP ('ijsland') "InitCap", 2 NLS_INITCAP ('ijsland', 'nls _ SORT = xdutch') as "linguistic-sensitiv" www.2cto.com 3 FROM DUAL; initCap linguis ------- Ijsland IJsland SQL> LPAD (expr1, n) LPAD (expr1, n, expr2) RPAD (expr1, n) RPAD (expr1, n, expr2) LPAD left fill RPAD right fill Example 2: Demo LPAD and RPAD. SQL> SELECT LPAD ('page 1', 15), 2 LPAD ('page 1', 15, '* $'), 3 RPAD ('page 1', 15 ), 4 RPAD ('page 1', 15, '* $') 5 from dual; LPAD ('page1 ', 15 LPAD ('page1', 15 RPAD ('page1 ', 15 RPAD ('page1 ', 15 --------------- --------------------- Page 1 * $ * Page 1 Page 1 * $ * SQL> www.2cto.com LTRIM (char, set) LTRIM (char) RTRIM (char, set) RTRIM (char) TRIM (trim_source) TRIM (trim from trim_source) TRIM (LEADING, trim_character FROM trim_source) TRIM (TRAILING, trim_character FROM trim_source) TRIM (BOTH, trim_character FROM trim_source) LTRIM removed FROM left RTRIM removed FROM right TRIM removed FROM left and right
Example 3: demonstrate LTRIM and RTRIM. SQL> SELECT 'page 1' as "src", 2 LTRIM ('page 1', 'P') as "ltrim 'P'", 3 LTRIM ('page 1 ', 'pa') as "ltrim'pa'", 4 RTRIM ('page 1', 'A') as "rtrim 'P'" 5 from dual; src ltrim 'P' ltrim' Pa 'rtrim 'P' ------ ---------- Page 1 age 1 ge 1 Page 1 www.2cto.com SQL> Example 4: Demo TRIM. The employees table is in Oracle OE schema. SQL> SELECT employee_id, 2 hire_date as "hiredate", 3 TO_CHAR (TRIM (0 FROM hire_date) as "Trim 0", 4 TO_CHAR (TRIM (LEADING 0 FROM hire_date )) as "TrimLeading 0", 5 TO_CHAR (TRIM (TRAILING 0 FROM hire_date) as "TrimTrailing 0", 6 TO_CHAR (TRIM (BOTH 0 FROM hire_date )) as "TrimBoth 0" 7 FROM employees 8 WHERE department_id = 60 9 order by employee_id; EMPLOYEE_ID hiredate Trim 0 TrimLeadin TrimTraili Tri MBoth 0 ----------- ---------- ------------ ---------- 103 month-90 month-90 month-9 month-90 month-90 month-9 month-9 month-9 month-9 month-91 month-91 month-5 month-91 month-91 month -91 21-5 months-91 21-5 months-91 105 25-6 month-97 25-6 month-97 25-6 month-97 25-6 month-97 25-6 month-97 106 05-2 month-98 5-2 month-98 5-2 month-98 05-2 month-98 5-2 month-98 107 07-2 month-99 7-2 month-99 7-2 month-99 07-2 month-99 7-2 month-99 SQL> note: the result date is 0. NLSSORT (char, 'nlsparam') NLSSORT (char) specifies sensitive sorting in a specific language. Example 5: demonstrate NLSSORT. Create the required table test first. The Code is as follows: SQL> CREATE TABLE test (name VARCHAR2 (15); the TABLE has been created. SQL> INSERT INTO test VALUES ('gaardiner'); 1 row has been created at www.2cto.com. SQL> INSERT INTO test VALUES ('gaberd'); 1 row has been created. SQL> INSERT INTO test VALUES ('gaasten'); 1 row has been created. SQL> SELECT * FROM test order by name; NAME --------------- GaardinerGaastenGaberd SQL> If the specified language is sensitive to sorting. The Code is as follows: SQL> SELECT * FROM test order by nlssort (name, 'nls _ SORT = XDanish '); NAME ------------- GaberdGaardinerGaasten SQL> Example 6: demonstrate how to use NLSSORT in a comparison operation. SQL> SELECT * FROM test WHERE name> 'gaberd' 2 ORDER BY name; SELECT * FROM test WHERE NLSSORT (name, 'nls _ SORT = XDanish ')> 2 NLSSORT ('gaberd', 'nls _ SORT = XDanish ') 3 order by name; NAME ------------- www.2cto.com SQL> REPLACE (char, search_string) REPLACE (char, search_string, replacement_string) REGEXP_REPLACE (source_char, pattern) REGEXP_REPLACE (source_char, pattern, replace_s Tring) REGEXP_REPLACE (source_char, pattern, replace_string, position) returns (source_char, pattern, replace_string, position, substring) REGEXP_REPLACE (source_char, pattern, replace_string, position, substring, substring) REPLACE. REGEXP_REPLACE is replaced by a regular expression. Example 7: demonstrate REPLACE. SQL> SELECT REPLACE ('Jack and JUE ', 'J') "remove", 2 REPLACE ('Jack and JUE', 'J', 'bl ') "replace" 3 from dual; remove change ---------- ------------ ACK and ue black and blue SQL> Example 8: demonstrate REGEXP_REPLACE. The employees and countries tables are located in Oracle OE schema. This example shows how to retrieve the phone number in the phone_number field, such as xxx. xxx. xxxx, and format it as (xxx) xxx-xxxx. SQL> SELECT 2 REGEXP_REPLACE (phone_number, 3' ([[: digit:] {3 })\. ([[: digit:] {3 })\. ([[: digit:] {4}) ', 4' (\ 1) \ 2-\ 3') "REGEXP_REPLACE" 5 FROM employees 6 order by "REGEXP_REPLACE "; REGEXP_REPLACE partition (515) 123-4444 (515) 123-4567 (515) 123-4568 (515) 123-4569 (515) 123-5555 demonstrate checking the country_name field. For each non-empty value of a field, a space is inserted between characters. SQL> SELECT 2 REGEXP_REPLACE (country_name ,'(.) ',' \ 1') "REGEXP_REPLACE" 3 FROM countries; REGEXP_REPLACE--------------------------------------------------------------------------------A r g e n t I n aA u s t r a l I AB e l I u mB r a z I lC a n a www.2cto.com SQL> demo check string, search for two or more spaces. Replace unnecessary spaces with one space. SQL> SELECT 2 REGEXP_REPLACE ('192 Oracle Parkway, Redwood Shores, ca', 3' () {2,} ', '')" REGEXP_REPLACE "4 FROM DUAL; REGEXP_REPLACE--------------------------------------500 for Oracle Parkway, Redwood Shores, ca SQL> SUBSTR (char, position) SUBSTR (char, position, substring_length) SUBSTRB (char, position) SUBSTRB (char, position, substring_length) SUBSTRC (char, position) SUBSTRC (char, position, substring_length) SUBST R2 (char, position) SUBSTR2 (char, position, substring_length) SUBSTR4 (char, position) SUBSTR4 (char, position, substring_length) REGEXP_SUBSTR (source_char, pattern) REGEXP_SUBSTR (source_char, pattern, position) REGEXP_SUBSTR (source_char, pattern, position, substring, match_param, subexpr) SUBSTR * by position A transcode string. REGEXP_SUBSTR * matches the substring according to the regular expression. Example 9: demonstrate SUBSTR and REGEXP_SUBSTR. SQL> SELECT SUBSTR ('abcdefg', 3, 4) as "pos34sub", 2 SUBSTR ('abcdefg',-5, 4) as "pos-54sub" 3 REGEXP_SUBSTR ('192 Oracle Parkway, Redwood Shores, ca', ', [^,] +,') "REGEXPR_SUBSTR" 4 from dual; pos34sub pos-54sub REGEXPR_SUBSTR ---------- ------------------- CDEF, Redwood Shores, SQL> www.2cto.com SOUNDEX (char) allows you to compare such words, their spelling is different, but English pronunciation is very similar. Example 10: demonstrate SOUNDEX. SQL> SELECT last_name, first_name 2 FROM hr. employees 3 where soundex (last_name) = SOUNDEX ('smythe ') 4 order by last_name, first_name; LAST_NAME FIRST_NAME contains invalid Smith LindseySmith William SQL> TRANSLATE (expr, from_string, to_string) replace. Replace from_string with to_string. Example 11: demonstrate TRANSLATE. SQL> select translate ('SQL * Plus User's Guide',' * ',' _ '), 2 TRANSLATE (' SQL * Plus User's Guide ', '*/''',' ___ ') 3 from dual; TRANSLATE ('SQL * plusus translate (' SQL * PLUSU certificate -------------------- SQL _Plus_User's _ Guide SQL _Plus_Users_Guide www.2cto.com SQL> indicates that the former replaces space and asterisk * With _; the latter, replace space, asterisk *, and single quotation mark (') _. TREAT this function changes the expression to a declared type. This function must have the EXECUTE object permission. Appendix ASCII (Note: hexadecimal tabulation, for example, if the hexadecimal 41 is)