ORACLE functions (1)

ASCII (x1) [function]: returns the ASCII value of the leftmost character of a character expression. [Parameter]: x1, character expression [Return]: Numeric [Example] SQL> select ascii ('A') A, ascii ('A') A, ascii ('') space, ascii ('shit') hz from dual; a space hz --------- 65 97 32 51902 [description] In the ASCII () function, A string of only numbers can be enclosed by '', but other characters must be enclosed by''. Otherwise, an error occurs. If the leftmost side is a Chinese character, only the ASCII code of the leftmost half-edge character of the Chinese character is used. [inverse function]: chr ()

CHR (n1) [function]: converts an ASCII code to a character. [Parameter]: n1, 0 ~ 255, integer [Return]: Random [Example] SQL> select chr (54740) zhao, chr (65) chr65 from dual; ZH C ---zhao A [reciprocal function]: ASCII

CONCAT (c1, c2) [function] connects two strings [parameter] c1, c2 struct expression [Return] struct: c1 | c2 [Example] select concat ('010-', '000000') |' to 23' Gao Qian's competing phone number from dual; Gao Qian's competing phone number -------------- 010-88888888 to 23

INITCAP (c1) [function] returns a string and converts the first letter of the string to uppercase, And the other letters to lowercase; [parameter] c1 plain expression [Return] plain type [Example] SQL> select initcap ('Smith abc abc') upp from dual; UPP ----- smith aBC Abc

LOWER (c1) [function]: Convert all strings to lowercase [parameter]: c1, character expression [Return]: character type [Example] SQL> select lower ('aabbccdd ') aaBbCcDd from dual; AABBCCDD -------- aabbccdd [similar] UPPER () converts all strings into uppercase.

UPPER (c1) [function] converts all strings to uppercase [parameter] c1, character expression [Return] character type [Example] SQL> select upper ('aabbccdd') upper from dual; UPPER -------- AABBCCDD [similar] LOWER () converts all strings to lowercase letters

NLS_INITCAP (x [, y]) [function] returns a string and converts the first letter of the string to uppercase, And the other letters to lowercase. [parameter] x-character expression [parameter] Nls_param is optional, to query data-level NLS settings: select * from nls_database_parameters; for example, specify the sorting method (nls_sort = ). Nls_sort = SCHINESE_RADICAL_M (radical, stroke) nls_sort = SCHINESE_STROKE_M (stroke, radical margin (pinyin) [Return] stroke type [Example] select nls_initcap ('AB cde') "test ", nls_initcap ('a c B d E', 'nls _ sort = SCHINESE_PINYIN_M ') "test1" from dual; Return: AB Cde, a c B d e select nls_initcap ('AB cde') "test", nls_initcap ('a c B d E', 'nls _ LANGUAGE = AMERICAN ') "test1" from dual;

NLS_LOWER (x [, y]) [function] returns a string and converts it to lowercase. [parameter] x-ray expression [parameter] Nls_param (optional, specify the sorting method (nls_sort = ). SCHINESE_RADICAL_M (radical, stroke) SCHINESE_STROKE_M (stroke, radical SCHINESE_PINYIN_M (pinyin) [Return] Random type [Example] select nls_LOWER ('AB cde') "test ", nls_LOWER ('a c B d E', 'nls _ sort = SCHINESE_PINYIN_M ') "test1" from dual; Return: AB cde, a c B d e

NLS_UPPER (x [, y]) [function] returns a string and converts the string to uppercase. [parameter] x regular expression [parameter] Nls_param (optional, specify the sorting method (nls_sort = ). SCHINESE_RADICAL_M (radical, stroke) SCHINESE_STROKE_M (stroke, radical SCHINESE_PINYIN_M (pinyin) [Return] Random type [Example] select NLS_UPPER ('AB cde') "test ", NLS_UPPER ('a c B d E', 'nls _ sort = SCHINESE_PINYIN_M ') "test1" from dual; Return: AB cde, a c B D e

INSTR (C1, C2 [, I [, J]) [function] searches for a specified character in a string and returns the location where the specified character is found; [note] multi-byte characters (Chinese characters, full-angle characters, etc.) are calculated based on 1 character. [parameter] Start position of the string C2 to be searched by C1, the default value is the position where J appears for the first time. The default value is 1 [Return] value [Example] select instr ('oracle traning', 'A', 1, 2) instring from dual; return: 9 [Example] select instr ('a software company in Chongqing ', 'mou',), Region B ('a software company in Chongqing', 'mou) instring from dual; Return: 3, 5

INSTRB (C1, C2 [, I [, J]) [function] searches for a specified character in a string and returns the location where the specified character is found; [note] multi-byte characters (Chinese characters, full-angle characters, etc.) are calculated by two characters. [parameter] the start position of the string C2 to be searched by C1, the default value is the position where J appears for the first time. The default value is 1 [Return] value [Example] select instr ('a software company in Chongqing ', 'mou ), instrb ('a software company in Chongqing ', 'mou',) instring from dual; return:

LENGTH (c1) [function] returns the LENGTH of a string. [description] multibyte characters (Chinese characters, fullwidth characters, etc ), calculate the [parameter] C1 string [Return] numeric type [Example] SQL> select length ('gao Qian competition'), length ('hai Ding district, Beijing '), length ('Beijing TO_CHAR ') from dual; length ('high Qian jing') length ('Beijing Hite area') length ('Beijing TO_CHAR ') ---------------------------- ------------------------ 3 6 9

LENGTH (c1) [function] returns the LENGTH of a string. [description] multibyte characters (Chinese characters, fullwidth characters, etc ), calculate the [parameter] C1 string [Return] numeric type [Example] SQL> select length ('high dry competition'), lengthB ('high dry competition') from dual; length ('high dry competition') lengthB ('high dry competition') --------------- ---------------- 3 6

LENGTHC (c1 ). length1 (c1 ). LENGTH4 (c1) [function] returns the length of a string. [description] multibyte characters (Chinese characters, fullwidth characters, etc ), calculate the [parameter] C1 string [Return] numeric type [Example] SQL> select length ('gao Qian competition'), length ('hai Ding district, Beijing '), length ('Beijing TO_CHAR ') from dual; In the character functions in Oracle, there is a type of function that calculates the character length, length, lengthB, lengthC, leng2, and length4 are commonly used in length and lengthB functions. Their meanings are: the number of characters returned by the Length function, use the defined character set to calculate the number of characters LENGTHB gives the byte LENGTHC of this string use Unicode leng22. use UCS2 LENGTH4 use UCS4 The following example is used: Select length ('hello ') from dual; 2 Select lengthB (' '), lengthC (''), leng22. length4 (' ') from dual;