Use of some Oracle functions

NVL function

The total value of a field is obtained by a query, and if the value bit is NULL, a default value is given.

SELECTNVL (sum(T.DWXHL),1) fromtb_jhdetwherezydm=-1

Here the usage of the NVL is concerned, NVL (Arg,value) represents if the value of the preceding ARG is NULL then the value returned is the following

ESCAPE

The Escape keyword is often used to make certain special characters, such as wildcards: '% ', '--' escapes the meaning of their original characters, and the escaped characters defined are usually used ' \ ', but sometimes other symbols can be used.

The escape character is D, and the escape function is not implemented;

SQL>Select*fromtestwheretestlike'sddd_%'escape  'd';

Find fields that contain all ' _ '.

SQL>Select*fromtestwheretestlike'%\_%' Escape ' \ ';

　Connection String Concat | |

--Concat can only connect two strings, connecting multiple calls that require nesting is inconvenientSql>Selectconcat ('AA','BB') fromdual; CONCAT ('AA','BB')-----------------AABB--|| Connect multiple strings directlySql>Select'AA'||'BB'||'cc'fromdual;'AA'||'BB'||'CC'----------------AABBCC

String interception

SUBSTR (string, intercept start position, intercept length)//return intercepted word

substr ('HelloWorld',0,1) // Returns the result of 'H'* starts with a string of length 1 from the first character of the string

length calculation function lengths () and LENGTHB ()

Sql>Selectlength ('Happy Spring Festival') fromdual; LENGTH ('Happy Spring Festival')------------------SQL>SELECTLENGTHB ('Happy Spring Festival') fromdual; LENGTHB ('Happy Spring Festival')-------------------difference: Length is the length of the character, LENGTHB is the length of the byte.

InStr Function

--Retrieves the index position of the specified character in the specified string

SQL>SELECTINSTR ('Hello','L') Fromdual;instr ('HELLO','L')------------------intercept the world string in HELLOWOLRD? SQL>SELECTSUBSTR ('HelloWorld', InStr ('HelloWorld',"')) fromdual; SUBSTR------World has selected 1 rows. SQL>SELECTSUBSTR ('HelloWorld', InStr ('HelloWorld',"')+1) fromdual; SUBST-----World has selected 1 rows. Lpad () rpad () Parameter 1: Specify the original string parameter 2: Fixed length parameter 3: filled character 

SQL>Selectlpad(' Jack ',Ten,' $ ')fromdual; lpad(' JACK',Ten,' $ ') ----------$$$$$ $jackSelectrpad(' Jack ',Ten,' $ ')fromdual; -----------jack$$$$$$

Next_day

Calculate the next one weeks what's the date?

Selectnext_day (sysdate,' Monday ') fromdual;

Round rounding

NVL (Expr1, EXPR2):

expr1 is null, returns EXPR2, is not NULL, returns EXPR1. Note that the two types should be consistent
NVL2 (Expr1, Expr2, EXPR3):

XPR1 is not NULL, returns EXPR2, or null, returning EXPR3. EXPR3 will be converted to EXPR2 type if the EXPR2 and EXPR3 types are different
Nullif (Expr1, EXPR2):

equality returns NULL, unequal return EXPR1

Decode ()

main role: Translation of query results into other values (that is, in other forms of expression, the following examples);

How to use:

Select decode (ColumnName, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)

From Talbename

Where ...

Where ColumnName is the column defined in the table to be selected,

• Explanation of Meaning:

Decode (condition, value 1, translation value 1, value 2, translation value 2,... The value n, the translation value n, the default value) is understood as follows:

if( bar = = value 1) Thenreturn( translate value 1) elsif( bar = = value 2) Thenreturn( translate value 2) ......elsif( bar = = value n) Thenreturn( translated value n) Elsereturn( lack of provincial value ) EndIf

Note: The default value can be either the column name you want to select, or the other values you want to define, such as other;

Table copy a fast-established table structure?

    Create Table  as Select *  from where 1 =  A ;     Insert  into Select *  from where deptno=;

Use of some Oracle functions