Problem: Oracle string function; Result: Oracle string function

Oracle String Functions

Recently changed the new company, and back to the Oracle database, a lot of things have forgotten, just have an impression, these two nights smoked a bit of time, the oracle of the string of some processing functions to do a bit, for later review.

Normally we use Oracle to have two main types of strings
1.char is always a fixed length, and if you set a value that is less than the Char column, Oracle automatically fills it with spaces. When comparing char, Oracle fills it with a space as equal length and then compares it.
The 2.VARCHAR2 data type is variable length, although it is synonymous with the varchar data type, but may change in future versions of Oracle, so you should avoid using varchar, preferably using VARCHAR2.
Fixed-length string fields use char, and all other string fields should use VARCHAR2.

Some of the functions of Oracle for string manipulation are listed below, and the following functions can be used on strings or on column names:
1.LOWER (String)Convert the input string to lowercase
2.UPPER (String)Converts the input string to uppercase
3.initcap (String)Converts the first letter of the input string word to uppercase. PS: If it is not two letters together, it is considered a new word, example: A_b A, a b similar to the previous cases, both A and B will be converted to uppercase
4.Connector (| |),Put two strings in | | Connected, you can also use the CONCAT function to concatenate strings. Example: Select CONCAT (city,country) from Table
5.Lpad and Rpad, the fill function, which is two very similar functions, is used exactly the same way. Allows a set of characters to be populated on the left [right] side of a column. Example: Rpad (city,20, '. '), if the value of ciy is not less than the length of 20, then use '. ' On the right, fill up to 20, if it is filled with a space, it looks like left-aligned.
6.Ltrim,rtrim and Trim, their role is to remove the unwanted characters from the left side of the string, the right side, and the left and then, by default deleting the space.
Format: RTRIM, LTRIM (string, ' delete character set ') the first parameter is the character to be deleted, the second parameter is a character set, and if you fill in ' AB ', Oracle will think of A and B, and then repeatedly check the right end of the string, Until each character in the string is deleted, that is, the deletion is stopped until the first character that does not appear in the deleted character set is encountered. This may be a bit less clear, see the example below
Original data
1 AAA.
2 AAA. "
3 AA. AAB
RTRIM (name, '. B "')
1 AAA
2 AAA
3 AA. Aa
If you feel that you are not clear enough, try a few more times to understand! I don't think it's as clear as it seems!
Trim format is a bit different, just look at the example bar Select Trim (' A ' from column name) from TableName or trim (leading ' a ' from column name) or trim (trailing ' a ' from column name) is equivalent to Ltri M is the same as the RTrim function, in fact the trim function is just to simplify the code.
7.LENGTHThe function is simple, which is to tell the user how long a string is, that is, how many characters are in the string, and that the function itself is not very useful, but can be used as part of other functions to calculate how many spaces a table requires or as part of an ORDER BY clause. PS: You cannot use a function such as length for a column that uses a Long data type.
8.SUBSTR (String,start [, Count])This function tells Oracle to extract a subset of string, start position, count fetch length, and not specify count, which is extracted to the end of the string by default.
Start can also be specified as a negative number, which is specified as a positive number from the beginning of the string (left), and when a negative number is specified, it is calculated from the end position (to the right). PS: Negative numbers cannot be used with char data types, because char is fixed-length, so they are populated with spaces until they extend to the full length of the column.
9.INSTRReturns the location where the specified string is located
INSTR (String,set[,start [, occurrence]]) If you specify start,oracle to skip all previous strings to that position to start the search, occurence, is forcing INSTR to skip the previous couple of matches to the string, Given the next matching position, if occurence specifies 3, that is the position of the third time. Example InStr (' Abacaaa ', ' a ', 2,2) matches a string from ABACAAA, starting at 2 positions, matching the position of the 2nd time A. PS: If a set has more than one character but a few characters, InStr gives the position of the first character in the character set.
10.CHR and ASCIIFunctions are seldom used in ad hoc queries. CHR converts a value into an equivalent ASCII string example: Select CHR from dual
The ASCII function executes exactly the opposite, passing it to the string, which is converted to the corresponding numeric value (only the first string is converted), and if you need to convert multiple strings at the same time, you can use the Dump functionDUMP(string)

The functions of Ps:instr,replace and substr have been extended to support regular expressions.

These functions can be used alone or in combination, which is a simple process that combines simple logical steps to accomplish very complex tasks.

There should be some log records of Oracle in the future.

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.

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.

Problem: Oracle string function; Result: Oracle string function