Oracle's string manipulation functions

Character function--return character value

These functions all receive parameters of the character family type (except CHR) and return the character value. In addition to the special instructions, most of these functions return a numeric value of type VARCHAR2. The return type of a character function is subject to the same restrictions as the base database type, such as: The VARCHAR2 value is limited to 2000 characters (4000 characters in ORACLE 8), and the char value is limited to 255 characters (2000 in ORACLE8). When used in procedural statements, they can be assigned to the VARCHAR2 or char type pl The/SQL variable.

The Chr () function converts ASCII code to character: character – ASCII;

The ASCII () function converts a character to an ASCII code: ASCII code-"character;

In Oracle, the CHR () function and ASCII () are a pair of inverse functions.

Chr () Function Example:

Sql> select CHR from dual;

CHR (65)
-------
A
Accii () Function Example:

Sql> Select ASCII (' A ') from dual;

ASCII (' A ')
----------
65
Remove the largest letter from the current field and calculate the next letter:

SELECT CHR (ASCII (Max (SUBSTR (uppercd,1,1))) +1)
From EXPS_CODE_LM

CHR

syntax : chr (x)

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.

Use Location: Procedural statements and SQL statements.

Concat

Syntax: CONCAT (STRING1,STRING2)

function: return string1, and connect string2 in the back.

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 alphanumeric character. Characters that are not letters do not change.

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.

Use location: procedural statements and SQL statements.

Nls_initcap

Syntax: Nls_initcap (String[,nlsparams])

function: returns the first letter of a string for each word capitalized and the other letters in the word lowercase string,nlsparams

A different sort sequence is specified that differs from the default value for the session. If you do not specify a parameter, the functionality and Initcap are the same. Nlsparams can be used in the form of:

' Nls_sort=sort '

Here sort developed a sequence of language sorts.

Use location: procedural statements and SQL statements.

Nls_lower

Syntax: Nls_lower (String[,nlsparams])

function: returns all letters in a string that are lowercase. Characters that are not letters are not changed.

The form of the Nlsparams parameter is the same as the use and the nlsparams parameter in the nls_initcap . If Nlsparams is not included, then Nls_lower does the same with LOWER .

use location, procedural statements, and SQL statements.

Nls_upper

Syntax: Nls_upper (String[,nlsparams])

function: returns a string in which all letters in the strings are in uppercase form. Characters that are not letters are not changed. The Nlsparams parameter is in the same form as the use and the nls_initcap . If no parameters are set, the Nls_upper function is the same as the upper.

Use location: procedural statements and SQL statements.

REPLACE

syntax: REPLACE (string,search_str[,replace_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 .

Use location: procedural statements and SQL statements.

Rpad

Syntax: Rpad (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.

Use location: procedural statements and SQL statements.

RTRIM

Syntax: RTRIM (String1,[,string2])

function: Returns the deletion of the character string2 appearing in string1 from the right. String2 is set to a single space by default. The database will scan the string1, starting from the right. When the first character that is not in string2 is encountered, the result is returned RTRIM behaves in a similar way to LTRIM .

Use location: procedural statements and SQL statements.

SOUNDEX

Syntax: SOUNDEX (String)

function: Returns the sound representation of a string. This is useful for comparing two words with different spelling but with similar pronunciation.

Use location: procedural statements and SQL statements.

SUBSTR Intercept sub-strings

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

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

Use location: procedural statements and SQL statements.

TRANSLATE

Syntax: TRANSLATE (STRING,FROM_STR,TO_STR)

function: Returns a string that replaces each character in the appearing from_str with the corresponding character in To_str. Translate is a superset of the functionality provided by replace. If FROM_STR is longer than TO_STR, the characters in From_str and not in To_str are removed from the string because they do not have the corresponding substitution characters. To_str cannot be empty. Oracle considers an empty string to be null, and if any of the arguments in translate are null, the result is null.

Use location: procedural statements and SQL statements.

UPPER

Syntax: UPPER (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.

Use location: procedural statements and SQL statements.

F.2 Character Function--Returns a number

These functions accept the character argument back to the numeric result. The parameter can be either char or VARCHAR2 type. Although many of the results are actually integer values, the returned results are simple number types, with no precision or scale range defined.

Ascii

Syntax: ASCII (String)

function: The database character set returns the decimal representation of the first byte of a string. Note that the function is still referred to as ASCII. Although many character sets are not 7-bit ASCII.CHR and ASCII are mutually opposite functions. CHR Gets the response character encoded by the given character. ASCII gets the character encoding of the given character.

Use location: procedural statements and SQL statements.

INSTR

Syntax: INSTR (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.

Use location: procedural statements and SQL statements.

INSTRB

Syntax: INSTRB (string1, string2[a,[b]])

function: the same as InStr, just the position of the manipulation of the parameter number character is byte.

Use location: procedural statements and SQL statements.

LENGTH

Syntax: LENGTH (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.

Use location: procedural statements and SQL statements.

Lengthb

Syntax: LENGTHB (String)

function: Returns the length of a string in bytes. LENGTHB and length are the same for single-byte character sets.

Use location: procedural statements and SQL statements.

Nlssort

Syntax: Nlssort (String[,nlsparams])

function: Gets the string byte that is used to sort string. All values are converted to byte strings, thus maintaining consistency across different databases. The role of Nlsparams is the same as in Nls_initcap. If parameters are omitted, the session uses the default sort.

Use location: procedural statements and SQL statements.

To_single_byte (c) Convert to half-width
To_multi_byte (c) convert to perfect corner

SELECT * FROM Testtree tt where length (tt.name)!=LENGTHB (tt.name)

Select InStr (' Corporatee floor ', ' or ') ' instring ' from dual; output is 2

Oracle's string manipulation functions