Oracle string replacement with spaces

 

Today, I learned how to remove spaces from strings in Oracle. Here is a summary. Two methods are available: trim function and replace function. I will introduce these two methods in detail below. I hope you can add them to your questions.

The TRIM function is used first. The TRIM function can delete any specified character. Removing spaces at the beginning and end of a string is the most frequently used trim function. Syntax trim (string). parameter string: string type. It specifies the string return value of the leading and trailing spaces to be deleted. If the function is successfully executed, a string with spaces at the header and tail of the string is deleted. If an error occurs, an empty string ("") is returned (""). If the parameter value is null, a null pointer exception is thrown. In Oracle, trim uses rtrim () and ltrim () to remove spaces on the right of the string and left of the string.

Of course, trim has more functions than that. I will introduce it in detail 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) do not use any parameters

SQL> select trim ('11') AA from dual;
 
AA
--
11

This is the most common method of using default parameters. By default, trim deletes spaces before and after strings.

2) If the both parameter is used, the effect is equivalent to that of method 1 without any parameters.

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

The "both" parameter removes the specified content before and after the string (by default, spaces are deleted ).

3) use the leading and trailing Parameters

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

SQL> select trim (trailing from '11') AA from dual;
 
AA
-----
11
The '-' separator is obtained from the result. It can be found that the leading parameter can remove spaces on the right side of the string, while the trailing parameter can remove spaces on the left side of the string. As Oracle provides rtrim () and ltrim ().

4) use the trim_character Parameter

The trim_character parameter changes the default behavior of "deleting spaces. If you want to delete the "X" before and after the string 'xxxxworldxxxx', the "trim_character" parameter will be used.

SQL> select trim ('x' from 'xxxxworldxx') AAAAA from dual;
 
Aaaaa
-----
World

Trim_character works with the "both", "trailing", and "leading" parameters as follows, which is similar to the previous demonstration. Check the results. Do not repeat them.

SQL> select trim ('x' from 'xxxxworldxx') AAAAA from dual;
 
Aaaaa
-----
World
 
SQL> select trim (both 'X' from 'xxxxworldxx') AAAAA from dual;
 
Aaaaa
-----
World
 
SQL> select trim (trailing 'X' from 'xxxxworldxx') AAAAA from dual;
 
Aaaaa
---------
Xxxxworld
 
SQL> select trim (Leading 'X' from 'xxxxworldxx') AAAAA from dual;
 
Aaaaa
---------
Worldxxxx

Note that the "trim_character" parameter can contain only one character and does not support multiple characters. Trim cannot meet our multi-character removal requirements, but we can use rtrim and ltrim for processing.
1) Use rtrim

SQL> select rtrim ('orxxxxworldxxxxor', 'or') AAAAA from dual;
 
Aaaaa
---------------
Orxxxxworldxxxx
2) use ltrim

SQL> select ltrim ('orxxxxworldxxxxor ',' or ') AAAAA from dual;
 
Aaaaa
---------------
Xxxxworldxxxxor
3) use the rtrim and ltrim functions together to achieve our goal.
SQL> select rtrim (ltrim ('orxxxxworldxxxxor ',' or '),' or ') AAAAA from dual;
 
Aaaaa
-------------
Xxxxworldxxxx

Note when using the rtrim and ltrim functions: "or" does not indicate that the entire "or" string is matched, but any character "O" or "R" is found to be deleted.

I will introduce the application of the TRIM function. The following describes the replace function. I personally think it is better to use the replace function when spaces are removed. The replace function replaces all the second given string expressions in the first string expression with the third expression. Let's take a look at the syntax description of the replace function: Replace ('string _ replace1 ', 'string _ replace2', 'string _ replace3 ')

'String _ replace1 ': the string expression to be searched for. string_replace1 can be character data or binary data.

'String _ replace2': the string expression to be searched for. string_replace2 can be character data or binary data.

String expression used to replace 'string _ replace3 '. string_replace3 can be character data or binary data.

Return type. If string_replace (1, 2, or 3) is one of the supported character data types, character data is returned. If string_replace (1, 2, or 3) is one of the supported binary data types, binary data is returned.

Here we still use space as an example.

SQL> select Replace ('aa KK ', '','') ABCD from dual;
 
ABCD
----
Aakk

Compared with the result of using the trim function, we can find that using the replace function can not only remove spaces at both ends of the string, but also remove spaces inside the string.

Of course, if you only want to remove spaces at both ends of the string, the efficiency of using the trim function will be higher.