ORACLE SQL single-line function (i) "Weber products must be a boutique"

1.SUBSTR: Find substrings in the parent string

SUBSTR (' HelloWorld ', 1,5)

1: Represents the starting position of the substring, if positive, positive, negative, reciprocal

5: The terminating position of the string, only the right number, can be omitted, if omitted is counted to the last

SUBSTR: The substring in the parent string SUBSTR (' HelloWorld ', 1,5) 1: Represents the starting position of the substring, if positive, positive, negative, minus 5: The terminating position of the string, only the right number, can be omitted, if omitted is the number to the last

2.LENGTH: Find the length of the string　　

Sql> Select LENGTH (' HELLOWORLD ') from dual; LENGTH (' HELLOWORLD ')--------------------          10

3.INSTR: Find the position of the substring in the parent string　

Select INSTR ('HelloWorld','W' from dual; INSTR ('HELLOWORLD','W')------- ----------------              6

4. Lpad and Rpad: formatted output

Sql>SelectLpad (Sal,Ten,'*'), Rpad (Sal,Ten,'*') fromEMP; Lpad (SAL,Ten,'*') Rpad (SAL,Ten,'*')---------------------------------------- ----------------------------------------******* -                                -************* the                                the************1250                               1250************2975                               2975************1250                               1250******

5. Replace : Replaces the substring in the parent string with another substring

Select REPLACE ('JACK and Jue','J','BL'   from dual; REPLACE--------------BLACK and BLUE

6. TRIM: Truncates a string from the parent string that is contiguous to the front and back:

Select TRIM ('H'from'hhhhelloworldhhh' from Dual TRIM ('H'F---------elloworld

7. two data types that represent characters:

VARCHAR2 (n): variable length, according to the length of the inserted data, to allocate the length

CHAR (n): fixed length, even if the inserted data is less than n, then Oracle will also allocate a length of n, not enough n with space

sql> CREATE TABLE t1 (ID number,name varchar2 (Ten)); Table created. SQL> CREATE TABLE t2 (ID number,nameChar(Ten)); Table created. SQL> INSERT into T1 values (1,'a'); 1row created. SQL> INSERT into T1 values (2,'b'); 1row created. SQL>commit; Commit complete. SQL> INSERT into t2 values (1,'a'); 1row created. SQL> INSERT into t2 values (2,'b'); 1row created. SQL> INSERT into t2 values (3,'C'); 1row created. SQL>commit; Commit complete. SQL>Select* fromT1; ID NAME---------- ----------1a2b SQL>Select* fromT2; ID NAME---------- ----------1a2b3C SQL>SelectT1.id,t2.name fromT1,t2whereT1.name=t2.name; no rows selected SQL>SelectT1.id,t2.name fromT1,t2whereT1.name=trim (t2.name);---This shows the scenario ID for trim () NAME---------- ----------1a2B

8. Numeric Functions

ROUND: Rounding a specified value

TRUNC: Truncation of the specified value

Select trunc (45.926,2), round (45.926,2 from dual; TRUNC (45.926,2) ROUND (45.926,2)-------------------------- ----          45.92           45.93

9.MOD: Returns the remainder after division calculation

Select mod (from  dual; MoD (+)-------------the        SQLselect mod ( , from  dual; MOD (-------------)      

Ten. Date Functions

Oracle saves dates in an internal format: century, year, month, day, hour, minute, second

Default format: DD-MON-RR

By specifying only the following two bits of the year, you can store the 21st century date in 20th century, in the same way that you can store the 20th century date in 21st century

Sysdate is a function that returns a date and time, whose value is derived from the operating system

Select  from dual; Sysdate----------aug-