Remove spaces from SQL, and remove spaces from SQL

Remove spaces from SQL, and remove spaces from SQL

 

I. Problem description:

The data stored in the nvarchar field in the database is as follows:

'3' * 10'

 

Ii. solution:

-- Test Case
Create table # temp
(Name nvarchar (20) null)
Insert into # temp select FecalColiform from WaterQuality
Select * from # temp

Update # temp set Name = replace (Name, '',''); --- 1
Update # temp set Name = replace (Name, '',''); --- 2
Update # temp set Name = replace (Name, '',''); --- 3
Update # temp set Name = replace (Name, '',''); --- 4

-- The result is valid.

-- Practical project application

Update WaterQuality set FecalColiform = replace (FecalColiform, '',''); --- 1
Update WaterQuality set FecalColiform = replace (FecalColiform, '',''); --- 2
Update WaterQuality set FecalColiform = replace (FecalColiform, '',''); --- 3
Update WaterQuality set FecalColiform = replace (FecalColiform, '',''); --- 4

 

You may have a question: why is the same operation repeated ??

A: ''It looks the same and actually different.

You need to completely copy the column and select all to check whether the concave and convex statuses behind them are empty.

Then copy the space that has not been modified and modify it.

For example, copy and put it in single quotes to observe '2. 0 × 10', then copy its unique '', and then execute a modification.

Before modification:

After modification:

 

Iii. failure experience:

1. trim Method

Select ltrim ('000000×10') -- remove spaces on the left
Select rtrim ('3. 3 × 10') -- remove spaces on the right.
Select ltrim (rtrim ('1970, 3.3 × 10') -- removes spaces at the beginning and end.

The result is valid.

But the actual data is like this '3. 3 × 10'

Select ltrim (rtrim ('3. 3 × 10') -- remove spaces at the beginning and end

The result is invalid.

 

References:

Https://zhidao.baidu.com/question/183968041.html

Https://www.cnblogs.com/TurboWay/p/5924445.html