The string processing function of Oracle

1. Convert string to uppercase

Format:

UPPER(string)

Give an example:

SQL> select UPPER('I am a student!') AS RESULT from dual;

RESULT
---------------
I AM A STUDENT!

2. Convert string to lowercase characters

Format:

LOWER(string)

Give an example:

SQL> select LOWER('I am a student!') AS RESULT from dual;

RESULT
---------------
i am a student!

3. Converts the first letter of each word in a string to an uppercase character

Format:

INITCAP(string)

Give an example:

SQL> select INITCAP('I am a student!') AS RESULT from dual;

RESULT
---------------
I Am A Student!

4. String truncation function

Format:

SUBSTR(string,start [,count])

Explain:
(1) If the count parameter is omitted, it is truncated to the end of the string;
(2) If the start parameter is negative, the intercept position is calculated from the right.

Give an example:

SQL> col RESULT format a20
SQL> select SUBSTR('I am a student!',3,2) as RESULT from dual;

RESULT
--------------------
am

SQL> select SUBSTR('I am a student!',3) as RESULT from dual;

RESULT
--------------------
am a student!

SQL> select SUBSTR('I am a student!',-8,7) as RESULT from dual;

RESULT
--------------------
student

SQL> select SUBSTR('I am a student!',-8) as RESULT from dual;

RESULT
--------------------
student!

5. Calculate string length

Format:

LENGTH(string)

Give an example:

SQL> select ename,LENGTH(ename) as name_length from emp where rownum <= 5;

ENAME	   NAME_LENGTH
---------- -----------
SMITH		     5
ALLEN		     5
WARD		     4
JONES		     5
MARTIN		     6

6. String connection

Format:

(1) string1 || string2
(2) CONCAT(string1,string2)

Give an example:

SQL> select concat('Henan ','Xinxiang') as RESULT from dual;

RESULT
--------------------
Henan Xinxiang

SQL> select 'Beijing,'||'China' as RESULT from dual;

RESULT
--------------------
Beijing,China

7. Find substring location

Format:

INSTR(string,substring[,start [,occurrence ] ] ) 

Explain:
(1) This function is used to find the position of the substring in the string. If the substring cannot be found, it returns 0;
(2) If there are multiple search results, the default is to return the position of the first substring from the left. If start is specified, the lookup starts from start. If occurrence is specified, the position of the substring of occurrence is returned;
(3) Start can be a negative value, indicating that the start position of the search is calculated from the right, and the substring is found from the right to the left.

Give an example:

SQL> select INSTR('The People Republic of China!','people') as RESULT from dual;

    RESULT
----------
	 0

SQL> select INSTR('The People Republic of China!','People') as RESULT from dual;

    RESULT
----------
	 5
	 
SQL> select INSTR('The People Republic of China!','e') as RESULT from dual;

    RESULT
----------
	 3

SQL> select INSTR('The People Republic of China!','e',4) as RESULT from dual;

    RESULT
----------
	 6

SQL> select INSTR('The People Republic of China!','e',1,2) as RESULT from dual;

    RESULT
----------
	 6

SQL> select INSTR('The People Republic of China!','o',-3) as RESULT from dual;

    RESULT
----------
	21

8. Delete first and last characters of string

Format:

LTRIM(string)
RTRIM(string)
TRIM(string)

TRIM(char from string)

Explain:
(1) First and last spaces are deleted by default;
(2) char can only be one character, not a string;
(3) Use [char from string] to delete any first and last characters.

Give an example:

SQL> select 'China,' || LTRIM(' Henan ') || ',Xinxiang.' as RESULT from dual;

RESULT
----------------------
China,Henan ,Xinxiang.

SQL> select 'China,' || RTRIM(' Henan ') || ',Xinxiang.' as RESULT from dual;

RESULT
----------------------
China, Henan,Xinxiang.

SQL> select 'China,' || TRIM(' Henan ') || ',Xinxiang.' as RESULT from dual;

RESULT
---------------------
China,Henan,Xinxiang.

SQL> select TRIM('*' from '**Henan*Xinxiang*') as RESULT1 from dual;

RESULT1
--------------------
Henan*Xinxiang

9. Fill character

Format:

LPAD(string,num,string2)
RPAD(string,num,string2)

Note: use string2 to fill the left or right side of the string, so that the total length of the string becomes num.

Give an example:

SQL> select LPAD('stu',10,'12') as RESULT from dual;

RESULT
----------
1212121stu

SQL> select RPAD('stu',10,'12') as RESULT from dual;

RESULT
----------
stu1212121

10. Replace string

Format:

REPLACE(string,string1,string2)

Description: replace all string1 in string with string2.

Give an example:

SQL> select REPLACE('I am a student!','student','teacher') as RESULT from dual;

RESULT
---------------
I am a teacher!

SQL> select REPLACE('aabbbccbb','b','1') as RESULT from dual;

RESULT
---------
aa111cc11