search

Home  >  Article  >  Database  >  Oracle remove string

Oracle remove string

WBOY
WBOYOriginal
2023-05-11 10:24:072555browse

In Oracle database, sometimes it is necessary to remove a certain part of the string, such as removing specific characters in a string, removing spaces before and after the string, etc. This article will introduce how to remove strings in Oracle.

  1. Remove specified characters from a string

In Oracle, you can use the REPLACE function to remove specified characters from a string. The REPLACE function allows the user to replace all certain characters in a string with another string.

The syntax is as follows: 

REPLACE(string, old_string, new_string)

Among them, string is the string to be replaced, old_string is the character to be replaced, and new_string is the replaced character.

For example, to remove the character "c" from the string "abcde", you can use the following statement: 

SELECT REPLACE('abcde', 'c', '') FROM dual;

The output result of the above statement is "abde".

  1. Remove spaces before and after a string

In Oracle, you can use the TRIM function to remove spaces before and after a string. The TRIM function can be used to remove spaces from a specified string, or to remove spaces from both sides of a string.

The syntax is as follows: 

TRIM([LEADING | TRAILING | BOTH] [trim_string] FROM string)

Among them, LEADING means removing spaces on the left, TRAILING means removing spaces on the right, and BOTH means removing spaces on both sides at the same time. trim_string can specify the string to be removed. If not specified, spaces will be removed by default.

For example, to remove the spaces before and after the string "abcde", you can use the following statement: 

SELECT TRIM('   abcde  ') FROM dual;

The output result of the above statement is "abcde".

  1. Remove letters and numbers from a string

In Oracle, you can use the REGEXP_REPLACE function to remove letters and numbers from a string. This function allows the user to replace characters in a string using regular expressions.

The syntax is as follows: 

REGEXP_REPLACE(string, pattern, '')

Among them, string is the string to be replaced, pattern is the regular expression to be matched, and the third parameter is the replaced character, here is the null character string.

For example, to remove all letters and numbers in the string "a1b2c3d4e5", you can use the following statement: 

SELECT REGEXP_REPLACE('a1b2c3d4e5', '[[:alpha:][:digit:]]', '') FROM dual;

The output result of the above statement is "".

  1. Remove Chinese characters from a string

In Oracle, you can use the NLSSORT function and REGEXP_REPLACE function to remove Chinese characters from a string.

The syntax is as follows: 

For example, to remove the Chinese characters in the string "abc Chinese def", you can use the following statement: 
The output result of the above statement is "abcdef". 


  1. Remove HTML tags from strings


In Oracle, you can use the DBMS_XMLGEN function and REGEXP_REPLACE function to remove HTML tags from strings. 


 The syntax is as follows: 
REGEXP_REPLACE(DBMS_XMLGEN.getXMLType(string).getStringVal(), '<[^>]+>', '')
 Among them, string is the string to be replaced, DBMS_XMLGEN.getXMLType(string).getStringVal() converts the string to XMLType type, 69844bcb8b5a415589022a080f73fbc2 is a regular expression that matches all HTML tags. 


For example, to remove all tags in the following HTML string: 
<body>
  <div class="container">
    <h1>Hello world!</h1>
    <p>This is a paragraph.</p>
  </div>
</body>
You can use the following statement: 
SELECT REGEXP_REPLACE(DBMS_XMLGEN.getXMLType('<body><div class="container"><h1>Hello world!</h1><p>This is a paragraph.</p></div></body>').getStringVal(), '<[^>]+>', '') FROM dual;
The output result of the above statement is "Hello world! This is a paragraph." 


Summary


This article introduces how to remove strings in Oracle. Whether it is to remove specified characters, remove spaces, remove letters and numbers, remove Chinese characters or remove HTML tags, Oracle provides corresponding functions and regular expressions. I believe that readers can choose and apply according to specific situations in practical applications. 






  1. > ↩



The above is the detailed content of Oracle remove string. For more information, please follow other related articles on the PHP Chinese website!
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：oracle installation stepsNext article：oracle installation steps
Related articles
See more