Oracle character functions

C HR , ASCII

syntax : CHR (string),ASCII (String)

function: Returns a character that has an equivalent value in the database character set and X. CHR and ASCII are a pair of inverse functions. After the conversion of CHR character after the ASCII conversion and then get the original character.

Example: SELECT ASCII (' a '), ASCII (' a '), Chr (' n '), Chr (' "") from dual;

Use Location: Procedural statements and SQL statements.

Concat

Syntax: CONCAT (STRING1,STRING2)

function: return string1, and connect string2 in the back, its function and "| |" The same.

Example: Select CONCAT (' AAA ', ' BBB '), ' aaa ' | | ' BBB ' from dual;//These two results are the same.

Use location: procedural statements and SQL statements.

Initcap

Syntax: Initcap (String)

function: returns the first letter of each word of a string capitalized while the other letter in the word is lowercase string. The word is separated by a. Space or an alphabetic character. Characters that are not letters do not change.

Example: Select Initcap (' Aa|aa ') from dual;//result is aa| Aa

Use location: procedural statements and SQL statements.

LTRIM

Syntax: LTRIM (STRING1,STRING2)

function: Returns the string1 that removes the characters from the left that appear in string2. The String2 is set to a single space by default. The database will scan the string1, starting from the far left. When the first character that is not in string2 is encountered, the result is returned. LTrim behaves in a manner similar to that of RTrim.

Example: Select LTRIM (' Abcdab ', ' ab ') from dual;//the result is Cdab

Select LTRIM (' Baabcdab ', ' ab ') from dual;//the result is CDBA

Select LTRIM (' Dbaabcdab ', ' ab ') from dual;//the result is Dbaabcdab

Use location: procedural statements and SQL statements.

REPLACE , TRANSLATE

syntax: REPLACE (string,search_str,replace_str),TRANSLATE (STRING,FROM_STR,TO_STR)

function: Replace all substrings in string search_str with optional replace_str, if no replace_str is specified, all substring search_str in string will be deleted. REPLACE is a subset of the functionality provided by TRANSLATE .

Example: Select REPLACE (' ADFASDADF ', ' a ', '-'), Translate (' ADFASDADF ', ' a ', '-') from dual;//results are-DF-SD-DF

Use location: procedural statements and SQL statements.

Rpad , Lpad

syntax: Rpad (string1,x[,string2]) , L PAD (string1,x[,string2])

function: Returns a string1 that inserts a string2 character in the X-character-length position. If the length of the string2 is less than the X character, copy as needed. If the string2 is more than x characters, only string1 the preceding x characters are used. If string2 is not specified, a space is used to populate it. X is to use the display length to be longer than the actual length of the string. Rpad behaves in a similar way to Lpad, except that it is populated on the right instead of on the left.

Example: SELECT lpad (' liunan86786786786 ', 10, ' * ') removes the character length and fills the specified character to the left,

Rpad (' Liunan ', 10, ' * ') removes the character length and fills the specified character to the left,

Rpad (Lpad (' Liunan ', 10, ' * '), 14, ' * ') paired with left and right padding

from dual;

Use location: procedural statements and SQL statements.

SUBSTR

Syntax: SUBSTR (String,a[,b])

function: returns a substring of string starting with the letter value A, b characters long. If a is 0, then it is considered to start with the first character. If positive, the return character is calculated from the left to the right. If B is a negative number, The returned character is computed from right to left, starting at the end of the string. If B does not exist, it will default to the entire string. If B is less than 1, then NULL is returned. If A or b uses a floating-point number, the value will first be an integer before processing.

Example: SELECT SUBSTR (' ADFADSFASD ', 4) from dual;//The result is ADSFASD

SELECT SUBSTR (' adfadsfasd ', -4) from dual;//the result is FASD

Use location: procedural statements and SQL statements.

UPPER , LOWER

Syntax: UPPER (String) , LOWER (String)

function: returns the uppercase string. Characters that are not letters are not changed. If the string is a char data type, the result is also of type char. If the string is of type VARCHAR2, then the result is VARCHAR2 type.

Example: SELECT UPPER (' ADFA '), LOWER (' ADFA ') from dual;

Use location: procedural statements and SQL statements.

INSTR , INSTRB

syntax: INSTR (string1, string2[a,b]) , INSTRB (string1, string2[a,[b]])

function: Get the position that contains string2 in string1 . String1 starts from the left to check, the starting position is a, if a is a negative number, then string1 is from the right to start scanning. The position of the second occurrence will be returned. Both A and B are set to 1 by default, which returns the position of the first occurrence of string2 in string1. If string2 is not found under the provisions of a and B, then return 0. The position is calculated relative to the beginning of the string1, regardless of the value of A and B.

INSTRB and InStr are the same, just the position of the manipulation of the parameter number character is byte.

Example: Select InStr (' Abcedfghedfghijk ', ' EDF ') from dual;//the result is 9

Use location: procedural statements and SQL statements.

LENGTH , LENGTHB

Syntax: LENGTH (String) , LENGTHB (String)

function: Returns the length of the byte unit of a string. A char value is a padding of the space type, and if the string is from the data type char, its trailing spaces are computed to the middle of the string length. If the string is null, the returned result is null instead of 0.

LENGTHB returns the length of a string in bytes. For single-byte character sets LENGTHB and length are the same.

Example: SELECT LENGTH (' Adsfad ') from DUAL;

Use location: procedural statements and SQL statements.

Oracle character functions