Function used to remove spaces in the SQL Server database

There are two functions, TTRIM () and LTRIM (), which can be used to cut spaces from the string. The LTRIM () function removes all spaces before the string; the RTRIM () function removes all spaces at the end of the string. Here is an example of any function RTRIM:
 
Select rtrim (site_name) FROM site_directory
 
In this example, if there are extra spaces at the end of a site name, the extra spaces will be deleted from the query results.
You can use nested functions to delete spaces before and after a string at the same time:
 
Select ltrim (RTRIM (site_name) FROM site_directory
 
You will find that these two functions are very useful when extra spaces are removed from CHAR fields. Remember, if you save a string in a CHAR field, the string will be appended with extra spaces to match the length of the field. With these two functions, you can remove useless spaces to solve this problem.

Use LTrim and RTrim as the leading and trailing spaces. For example:

LTrim (RTrim (Name ))

Use the replace function to replace the space in the middle. For example:

REPLACE (Name ,'','')

If it is a common space, it is easy to replace, but sometimes it is troublesome to encounter some special spaces. It looks like a common space, but it cannot be replaced.

This requires special methods to find spaces and replace them with replace.

To find such spaces, you need to use the ascii function to find the ascii value of the space, and then replace it with the replace function + char function.

For example, if the ascii value of a space is 9

REPLACE (Name, CHAR (9 ),'')

To replace.

According to the above method, it is nothing more than how to find the ascii value of space.
Assume that the Name has a value of "zhang san", followed by a special space.

SELECT ascii (REPLACE (name, 'Zhang San', '') FROM table name WHERE id = **

Take out the value of Name and replace the text part. The rest is the special space, and the ascii value of the space can be obtained through the ascii function.

Example replacement

1. Remove the character format of carriage return:

REPLACE (filed_name, CHAR (13) + CHAR (10), SPACE (0 ))
Filed_name indicates the field name of the database, which can be replaced when used. The usage example is as follows:
Update ieel00h set el_name1 = REPLACE (el_name1, CHAR (13) + CHAR (10), SPACE (0) where el_no like 'L %'
2. Remove space

You can use rtrim to remove spaces on the right and ltrim to remove spaces on the left. An example is as follows:
Update ieel00h set el_name1 = rtrim (el_name1) where el_no like 'L %'
Update ieel00h set el_name1 = Ltrim (el_name1) where el_no like 'L %'