String Learning for Oracle databases

One, virtual table

　　Dual, only for operation, no data.

Second, String function

　　1: string concatenation function concat (P1,P2) or | | ；

Eg: concatenation of the string "Hello" and "Kitty"

Select concat (' Hello ', ' Kitty ') from dual or select ' Hello ' | | ' ' | | ' Kitty ' from dual;

2: String length function long ();

Eg: the length of the statistical string "No Zuo No Die"

Select Length (' No Zuo no Die ') from dual;

3: string-complement function

Lpad: Left complement function (P1,N,P2), so that the P1 is left-justified, the total length is n, the length is not enough to use the string P2 to complement the position. (Right-aligned)

2--: Right complement function rpad (P1,N,P2), so that P1 is right-aligned, the total length is n, the length is not enough to use string P2 to complement. (left-justified)

Eg: View all employee positions, align right, make length 20, not enough to use # complement

Select Lpad (job,20, ' # ') from EMP;

4: String Case function

1--:lower (p1): Turn all the letters in P1 into lowercase

2--:upper (p1): Turns all letters in P1 into uppercase

3--:initcap (P1: Capitalize the first letter of each word

Eg: Change the string "HELLOWORLD" all to lowercase output

Select Lower (' HELLOWORLD ') from dual;

5: String truncation function

1--:trim (P2 from p1): Remove P1 before and after the P2,P2 can only be one character

2--:ltrim (P1,P2): Truncate the substring that matches the string in P2 before P1 string

3--:rtrim (P1,P2): A substring that matches the string in P2 after P1 string is truncated

Eg: Remove the string "sea water from the Sea of Shanghai"

Select Trim (' Sea ' from ' sea water ' from the Shanghai Sea ') from dual;

6: String substring function substr (P1,start[,len]), [] the expression within the [] can be saved, starting from start (including start) a substring start of 0 or 1 is the same, start can be a negative representation of the countdown beginning to the back of the substring, Len represents the length

Eg: the string "HelloWorld" is taken from the third character beginning with a length of 5 substring

Select substr (' HelloWorld ', 3,5) from dual;

7:--function InStr (P1,p2[[,m],n]) that finds a string in a string, returns where the P2 is at P1 where m indicates where the P1 is starting to retrieve n means that the first occurrence of P2 is not found in the return 0, does not write M and n, it means that the retrieval from the beginning, the position that appears, Write m does not write N, indicating that the search begins at M, where it first appears

Eg: find the first occurrence of the substring "no" in the string "No Zuo No Die"

Select InStr (' No Zuo no die ', ' no ') from dual;

At the end of the day, these are some of the common functions of the string, I think it is helpful to record Ah, like the words please click to praise it!!! Continue to update the method of the numeric function for everyone tomorrow.

String Learning for Oracle databases