Oracle Common function Usage Rollup

INSTR Usage:INSTR (string,substring,position,ocurrence)
Explanation: string: source string
SubString: substring to find
Position: The start position of the lookup. If the starting position is 0, the return value is 0, and when the starting position is negative, the search starts from the right.
Ocurrence: A substring of the first occurrence of a source string

Returns the location found (the string subscript is starting at 0, if found) and returns 0 if it is not found. The default lookup order is left to right.

SELECT INSTR (‘CORPORATE Floor‘,‘OR‘,0,1)FromDUAL; The return value is 0SELECT INSTR (‘CORPORATE Floor‘,‘OR‘,2,1)FromDUAL; The return value is 2SELECT INSTR (‘CORPORATE Floor‘,‘OR‘,2,2)FromDUAL; The return value is 5SELECT INSTR ( ' corporate floor ' corporate floor ' 

SUBSTR Usage:SUBSTR (string,start_position,[length]) to substring, return string
Explanation: String meta strings
Start_position start position (starting from 0)
Length optional, number of substrings

SELECT SUBSTR (‘This is a test‘,0,2) valueFromDual return value thSELECT SUBSTR (‘This is a test‘,1,from dual; return value Hiselect SUBSTR ( This is a test-1, 2) value from dual; return value Tselect SUBSTR ( This is a test-2, 2) value from dual; return Value St   

NVL Usage: NVL (EExpression1, EExpression2)

Returns a non-null value from two expressions. If the eExpression1 evaluates to a null value, NVL () returns EEXPRESSION2. If the EExpression1 evaluates to a value other than NULL, the EExpression1 is returned. EExpression1 and EExpression2 can be any data type. If the result of EExpression1 and eExpression2 are null values, then NVL () returns NULL.

 select NVL ( pos1  ", from dual; The return value is Pos1select NVL (null,  ' pos2 ") from dual; The return value is Pos1select NVL ( null,from dual; Return value is null                

Http://www.cnblogs.com/ningvsban/p/3586218.html
Http://www.cnblogs.com/qqzy168/archive/2013/05/28/3103085.html

In Oracle, there are three kinds of equals: <>,!=,^=

For example:

SELECT * FROM test where name<> ' xn '. The returned result is a record where name is not xn and name is not empty. But this is not the same as what we want to get, because our goal is to get all the records of name Xn, and of course this includes a record with the name empty, so these write SQL statements are problematic. In order to solve this problem, we can use the following two kinds of scenarios:

Select* from test where InStr ( Concat (Name, ' xx "), Span style= "color: #ff0000;" > ' xx ") <> ' xn   

Note: null can only be judged by IS null or is not NULL, and other operators and null operations are false.

　　String connection methods in each database

1) Mysql:concat ()

2) Oracle:concat (), | |

3) SQL Server: +

For example:

SELECT‘This is‘+ a Test ' | |  ' a test from dual; Return value this a test               

Http://www.cnblogs.com/ningvsban/p/3586223.html

Oracle Common function Usage Rollup