How to remove spaces in oracle

Oracle method to remove spaces: 1. Use the Trim function to remove the leading and trailing spaces in the string; 2. Use rtrim and ltrim to remove the left and right spaces; 3. Use the replace function to remove all spaces in the string.

The operating environment of this article: Windows 7 system, Dell G3 computer, Oracle 11g version.

How to remove spaces in oracle?

How to remove spaces from strings in oracle:

The first is the Trim function. The trim function has the function of deleting any specified characters, and removing spaces at the beginning and end of a string is the most frequently used trim function.

SyntaxTrim (string)

Parameter string: string type, specifies the string return value String to delete leading and trailing spaces.

When the function is successfully executed, it returns a string with the leading and trailing spaces removed from the string string. When an error occurs, it returns an empty string ("").

If the parameter value is null, a null pointer exception will be thrown.

In Oracle, the most commonly used forms of trim are rtrim() and ltrim(), which are to remove spaces on the right side of a string and to remove spaces on the left side of a string.

Of course, the function of trim is more than that. A detailed introduction will be given below. Here I will take the removal of string spaces as an example.

Let’s take a look at the syntax description of the trim function:

trim( [ {  {leading|trailing|both}  [trim_character]|trim_character}  from] trim_source

1), without using any parameters

SQL> select trim('   11   ') aa from dual;

11

This is the most common one The usage method uses the default parameters. By default, TRIM will delete the spaces before and after the string at the same time.

2), using both parameters, the effect is equivalent to method 1 without using any parameters

SQL> select trim(both from '   11   ') aa from dual;

11

The "BOTH" parameter means to remove the specified content before and after the string at the same time (Spaces are removed by default).

3), use the leading and trailing parameters

SQL> select trim(leading from '   11  ') aa from dual;

11

SQL> select trim(trailing from '   11  ') aa from dual;

11

Get the separator '-' from the result, you can find that using leading The parameter can remove the spaces at the right end of the string, and the trailing parameter can remove the spaces at the left end of the string. Just like rtrim() and ltrim() provided by oracle.

4), use trim_character parameter

trim_character parameter changes the default behavior of "remove spaces". If you want to remove the "x" that appears before and after the string 'xxxxWORLDxxxx', the "trim_character" parameter comes in handy.

SQL> select trim('x' from 'xxxxWORLDxxxx') aaaaa from dual;

WORLD

trim_character is used with the three parameters "both", "trailing" and "leading". The effect is as follows, similar to the previous demonstration. Let’s look at the results and won’t go into details.

SQL> select trim('x' from 'xxxxWORLDxxxx') aaaaa from dual;

WORLD

SQL> select trim(both 'x' from 'xxxxWORLDxxxx') aaaaa from dual;

WORLD

SQL> select trim(trailing 'x' from 'xxxxWORLDxxxx') aaaaa from dual;

xxxxWORLD

SQL> select trim(leading 'x' from 'xxxxWORLDxxxx') aaaaa from dual;

##WORLDxxxx

It must be noted that the "trim_character" parameter here only allows one character, and does not support multiple characters. Trim cannot meet our requirements for removing multiple characters, but we can use rtrim and ltrim to handle it.

1) Use rtrim

SQL> select rtrim('ORxxxxWORLDxxxxOR','OR') aaaaa from dual;

ORxxxxWORLDxxxx

# #2) Use ltrim

SQL> select ltrim('ORxxxxWORLDxxxxOR','OR') aaaaa from dual;

xxxxWORLDxxxxOR

3) Use the RTRIM and LTRIM functions jointly to achieve our goals

SQL> select rtrim(ltrim('ORxxxxWORLDxxxxOR','OR'),'OR') aaaaa from dual;

xxxxWORLDxxxx

##Use rtrim and Things to note when using the ltrim function: "OR" does not mean matching the entire "OR" string, but any character "O" or "R" found will be deleted. The application of the trim function has been introduced. Let’s introduce the replace function. I personally think that the replace function is more useful when removing spaces. . The replace function replaces all occurrences of the second given string expression in the first string expression with the third expression. Let’s look at the syntax description of the replace function: replace('string_replace1','string_replace2','string_replace3')

'string_replace1' The string expression to be searched, string_replace1 can be character data or binary data.

'string_replace2' The string expression to be found, string_replace2 can be character data or binary data.

'string_replace3' String expression for replacement, string_replace3 can be character data or binary data.

返回类型，如果 string_replace（1、2 或 3）是支持的字符数据类型之一，则返回字符数据；如果 string_replace（1、2 或 3）是支持的 binary 数据类型之一，则返回二进制数据。

 这里我们依然以去空格为例。

SQL> select replace('   aa  kk  ',' ','') abcd from dual;

aakk

       与使用trim函数的结果进行对比，我们可以发现，使用replace函数不仅可以去除字符串两端的空格，也可去除字符串内部的空格。

       当然，如果只是要去除字符串两端的空格，使用trim函数效率会更高。

相关推荐：oracle数据库学习教程

The above is the detailed content of How to remove spaces in oracle. For more information, please follow other related articles on the PHP Chinese website!