Shulou(Shulou.com)06/01 Report--

Editor to share with you what the character manipulation functions in Oracle are, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Character operation function CONCAT

The CONCAT function concatenates two character literals, columns, or expressions to generate a larger word character expression. Grammar: CONCAT (S1 and S2)

Select concat ('Today is:',SYSDATE) FROM DUAL

CONCAT can only use two parameters

Select concat ('Outer1', concat ('Inner1',' Inner2')) from dual

LENGTH

The LENGTH function returns the number of characters that make up the string. Spaces, tabs, and special characters are counted by the LENGTH function. There is only one parameter, LENGTH (s). (tabulator operator 1)

Select length ('ab cd e') from dual

LPAD and RPAD

The LPAD (RPAD) function returns a composite string formed after the left (right) side of a given string is filled with a specified number of characters. The strings used for padding include character literals, column values, expressions, spaces (default), tabs, and special characters.

The LPAD and RPAD functions take three arguments, and the syntax is RPAD (sjournal njournal p) and LPAD (sjournal njournal p). S represents the source string, n represents the final length of the returned string, and p specifies the string to be populated.

Select LPAD ('abc',6,'*'), RPAD (' abc',6,'*') from dual

TRIM

The TRIM function removes some characters from the beginning or end of the character value, generating a shorter character entry from the face.

The arguments used by the TRIM function consist of a mandatory component and an optional component. Syntax: TRIM ([trailing | leading | both] trimstring from s). The trimmed string (s) is mandatory. Only one correction character can be specified.

TRIM (s) removes spaces on both sides of the input string.

TRIM (trailing trimstring from s) removes all trimstring, if any, from the end of the string s.

TRIM (leading trimstring from s) removes all trimstring, if any, from the beginning of the string s.

TRIM (both trimstring from s) removes all trimstring, if any, from the beginning and end of the string s.

Select trim (both'* from'* Hidden*'), trim (leading'* 'from' * Hidden*'), trim (trailing'* 'from' * Hidden*'), trim (both from 'Hidden'), trim (trailing from' Hidden'), trim (leading from 'Hidden') from dual

RTRIM

The RTRIM function removes some characters from the end of the character value, resulting in a shorter string.

Syntax: RTRIM (string [, trimstring]), the trimmed string is required and multiple characters can be deleted. Spaces are deleted by default.

Select rtrim ('abcd') from dual

Select rtrim ('abcd***','*') from dual; select rtrim (' abcd*#','*#') from dual

LTRIM

The LTRIM function removes some characters from the beginning of the character value, resulting in a shorter string.

Syntax: RTRIM (string [, trimstring]), the trimmed string is required and multiple characters can be deleted. Spaces are deleted by default.

Select ltrim ('abcd') from dual

Select ltrim ('* abcd','*') from dual; select ltrim ('* # abcd','*#') from dual

INSTR

The INSTR function determines the position of the search string within a given string. It returns the numeric position where the search string begins to appear for the nth time (relative to the specified starting position). Returns 0. 0 if the search string does not exist.

The INSTR function takes two optional parameters and two mandatory parameters. Syntax: INSTR (source string,search string, [search start position], [nth occurrence]). The default value for search start position is 1 or the beginning of source string. The default value for nth occurrence is 1 or appears for the first time.

Select instr ('1, 3, 5, 7, 9, 5, 7, 9, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 3, 5, 7, 9, 5, 3, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5

From the first character on the left to the right, return to the position where'# 'first appeared.

Select instr ('1, 3, 5, 7, 9, 5, 3, 5, 7, 9, 7, 9, 5, 5, 5, 5, 5, 3, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5

From the fifth character on the left to the right, return to the position where'# 'first appeared.

Select instr ('1, 3, 5, 7, 9, 5, 7, 9, 7, 9, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 4, 5, 5, 5, 7, 9, 4, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 7, 9

From the third character on the left to the right, return to the position where'# 'appears for the fourth time.

Select instr ('1, 3, 5, 7, 9, 7, 7, 9, 7, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9

From the third character on the left to the right, return to the position where'# 'appeared for the 10th time, no return 0.

Select instr ('1, 3, 5, 7, 9, 1, 3, 5, 7, 9, 7, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9

From the first character on the right to the left, return to the position where'# 'first appeared.

Select instr ('1, 3, 5, 7, 9, 1, 3, 5, 7, 9, 7, 9, 9, 5, 5, 5, 9, 5, 5, 5, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 3, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5

From the first character on the right to the left, return to the position where'# 'appears for the third time.

Select instr ('1, 3, 5, 7, 9, 5, 7, 9, 7, 9, 5, 5, 5, 9, 5, 3, 5, 3, 3, 3, 3, 3, 5, 3, 3, 3, 3, 5, 5, 5, 7, 9, 4, 3, 3, 3, 3, 3, 5, 5, 5, 7, 9, 3, 3, 3, 3, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 5, 7, 9, 5, 5, 5, 5, 7, 9, 5, 5, 5, 5, 5, 5

From the third character on the right to the left, return to the position where'# 'appears for the third time.

SUBSTR

The SUBSTR function extracts a string of the specified length, starting at a given position in the source string. If the starting position is greater than the length of the source string, null is returned. If the number of characters extracted from a given starting position is greater than the length of the source string, the returned part is the substring from the starting position to the end of the string.

The SUBSTR function takes three arguments, the first two of which are mandatory. Syntax: SUBSTR (source string,start position, [number of characters to extract]). The default number of characters to extract is the number of characters from start position to the end of source string.

Select substr ('1, 3, 5, 7, 9, 5) from dual

Extract from the fifth character from left to right, from left to right, to the end of the source string.

Select substr ('1, 3, 5, 7, 9, 7, 9, 5, 5, 3, 5, 3, 5, 5, 5, 9, 5, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 3, 5, 5, 7, 9

Start with the fifth character from left to right, extract from left to right, and extract 3 characters.

Select substr ('1, 3, 5, 7, 9, 5, 7, 9, 5, 9, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 3, 5, 9, 5, 3, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 9, 5, 9

Start with the third character from right to left, extract from left to right, and extract 2 characters.

Select substr ('1, 3, 5, 7, 9, 5, 3, 5, 9, 9, 4, 3, 5, 9, 4, 5, 9, 4, 5, 9, 4, 5, 9, 4, 5, 9, 4, 5, 9, 4, 5, 5, 9, 4, 5, 5, 9, 4, 5, 9, 4, 3, 4, 3, 4, 3, 4, 3, 4, 4, 4, 4, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4

Vbl.

REPLACE

The REPLACE function replaces all search items that appear in the source string with substitutes. If the length of the replacement item is different from the length of the search term, the length of the returned string is also different from the length of the source string. If the search string is not found, the source string is returned intact.

The REPLACE function takes three arguments, the first two of which are mandatory. Syntax: REPLACE (source string,search item [, replacement term]) If you omit the replacement term parameter, all occurrences of search item are removed from the source string.

Select replace ('1, 3, 5, 7, 9, 1, 3, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9

Select replace ('1, 3, 5, 7, 9, 5, 7, 9, 5, 7, 9, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 3, 5, 7, 9, 5, 3, 5, 7, 9, 5, 5, 7, 9, 5, 5, 7, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 9, 5, 5, 7, 9, 5, 5

These are all the contents of the article "what are the character manipulation functions in Oracle?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.