Today I will talk to you about the ways to remove spaces in oracle, from simple to complex, there must be a way to solve your problem.
Method 1: Use the TRIM() function
select trim('This is a string') as zfc from dual;
--enter：'This is a string'
--Output result：'This is a string'
analyze:The function of the trim() function is to remove the spaces at both ends of the string, and the middle space cannot be removed.
Method 2: Use replace() function
select replace('This is a string',' ','') as zfc from dual;
--enter:'This is a string'
--Output result:'This is a string'
**Analysis: The **replace function has been introduced in previous articles. A simple understanding is that it is a string replacement function, replacing the specified substring with a new substring.
Put it in the above statement, replace '''' and replace the space with null.
Comparing replace() and rim(), trim() only removes spaces at both ends of the string, replace() replaces spaces in the entire string, and is selectively used according to different scenarios.
Method 3: Also use the replace() function
select
replace(replace(replace(replace('This is a string',chr(9),''),chr(10),''),chr(13),''),chr(32),'')
as zfc
from dual;
--chr(9) Tab symbols
--chr(10) Line breaks
--chr(13) Carriage return symbol
--chr(32) Space character
--enter：'This is a string'
--Output result：'This is a string'
**Analysis: The principle of the ** statement is to replace it, replace it with empty, but this time the replacement objects are tab characters, line breaks, carriage return characters, and space characters.
chr() function: This function returns characters encoded with numeric expression values (that is, converting ASCII code into characters). Don’t care about this function, just understand the meaning of the representative. If you are interested, you can learn more about it.
chr(9) stands for tab character
chr(10) represents line break
chr(13) represents carriage return
chr(32) represents space character
Whether it is tab characters, line breaks, carriage return characters, or space characters, fields are very similar to spaces when outputting fields in the database, so many friends mistakenly treat them as spaces with simple replacement of trim() function or replace(), and the reason why they cannot be removed.
Summarize
This is the end of this article about three ways to remove spaces by oracle. For more related content on how to remove spaces by oracle, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!