Oracle "To_number" "InStr"

Requirement: Sort a string that contains numbers

After search, refer to http://www.cnblogs.com/m-cnblogs/archive/2012/03/30/2425938.html

Screenshot:

(To_number (str,split 1,...) InStr () Replace () substr ())

TO_NUMBER(x [, format], [ nls_language ])

Converts x to a NUMBER .

• X is the string, that would be, converted to a number.
• Format, optional, is the format, which is being used to convert X to a number.
• Nls_language, optional, is the NLS language used to convert X to a number

INSTR (String, Pattern-to-find)

INSTRReturns the location (beginning) of a pattern in a given string. Its simple form is:

The general syntax of INSTR is:

INSTR (string to search, search pattern [, start [,occurrence]])

The arguments within brackets ([]) is optional.

Finds the specified character in a string, returning the position of the specified character that was found.

If the value of start is negative, the lookup is made from right to left, but the location data is still calculated from left to right.

SUBSTRRetrieves a portion of the string. The general format for this function is:

SUBSTR(string, start_at_position[, number_of_characters_to_retrieve])
Returns a substring from the given character expression.
REPLACE (source-string, Pattern-to-find, pattern-to-replace-by)

(to) ' 2011-10-11 ' 2011/10/11 ' conversion

Update Table 1 T set t. Column 1=replace ((select column 1from table 1 A where a. primary key column =t. Primary key column), '/', '-')

Example: Select replace (' 111222333444 ', ' 222 ', ' 888 ') from dual;

Output is ' 111888333444 '

and finally My Code

Pn
To_number (str, '-')

Str:

Replace (source, '-')

Source

substr (PN,INSTR (pn, '-') +1, Length (PN) -1-instr (pn, '-'))

Oracle "To_number" "InStr"