Usage of InStr, substr, and NVL in Oracle

Tag: Specify the default Val floor post data type rate this he

Usage of InStr, substr, and NVL in Oracle

INSTR Usage: INSTR (source string, the string to find, starting with the first few characters, to find the ordinal number of the first match)

Returns the location found and returns 0 if it is not found. The default lookup order is left to right. When the starting position is negative, start looking from the right. If the starting position is 0, the return value is 0.

SELECTINSTR ('CORPORATE Floor','OR',0,1) fromDUAL; The return value is 0SELECTINSTR ('CORPORATE Floor','OR',2,1) fromDUAL; The return value is 2SELECTINSTR ('CORPORATE Floor','OR',2,2) fromDUAL; The return value is 5SELECTINSTR ('CORPORATE Floor','OR',-1,1) fromDUAL; return value isSELECTINSTR ('CORPORATE Floor','OR',-5,1) fromDUAL; The return value is 5

SUBSTR Usage: SUBSTR (source string, find starting position, [length])

The return value is a string that specifies the starting position and length in the source string.

SELECTSUBSTR ('This is a test',0,2) value fromdual; return value thSELECTSUBSTR ('This is a test',1,2) value fromdual; return value HiSELECTSUBSTR ('This is a test',-1,2) value fromdual; return value TSELECTSUBSTR ('This is a test',-2,2) value fromDual 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',null from dual; return value is POS1  SELECT NVL (null,'pos2' from dual; return value is POS1  SELECT NVL (null,null from dual;    return value is null

Usage of InStr, substr, and NVL in Oracle