使用SQL中的过程从存储的表行中删除空间

时间:2021-08-05 03:34:06

I was looking for a stored procedure for SQL server 2005 to remove leading, trailing and other spaces of records stored in the table.

我正在为SQL server 2005寻找一个存储过程,以删除表中存储的领先、尾随和其他记录空间。

Basically, I needed to bulk insert data into my Database, and the table that I am working on contains blank spaces in various records. Take an instance of Pincode/Zip code, that cannot have spaces between them but the data I have inserted had spaces in most of the pincodes like "110 001", however it should be "110001". In other columns the data is in form of " data " where it should be "data"

基本上,我需要将数据批量插入到数据库中,我正在处理的表在不同的记录中包含空格。以Pincode/Zip代码为例,它们之间不能有空格,但是我插入的数据在大多数Pincode中都有空格,比如“110001”,但是应该是“110001”。在其他列中,数据以“数据”的形式出现,而数据应该是“数据”

I have already inserted the data but, I need to format it now, so as to arrange it in proper form.

我已经插入了数据,但是我现在需要格式化数据,以便以合适的形式安排。

2 个解决方案

#1


3  

You should use REPLACE . Try

您应该使用替换。试一试

REPLACE(Pincode, ' ', '')

替换(Pincode”、“,”)

UPDATE:

更新:

If you need to update data in a table

如果需要更新表中的数据

UPDATE TableName
SET Pincode = REPLACE(Pincode, ' ', '')

#2


1  

Use UPDATE with Replace function http://msdn.microsoft.com/en-us/library/ms186862.aspx

使用UPDATE with Replace函数http://msdn.microsoft.com/en-us/library/ms186862.aspx

#1


3  

You should use REPLACE . Try

您应该使用替换。试一试

REPLACE(Pincode, ' ', '')

替换(Pincode”、“,”)

UPDATE:

更新:

If you need to update data in a table

如果需要更新表中的数据

UPDATE TableName
SET Pincode = REPLACE(Pincode, ' ', '')

#2


1  

Use UPDATE with Replace function http://msdn.microsoft.com/en-us/library/ms186862.aspx

使用UPDATE with Replace函数http://msdn.microsoft.com/en-us/library/ms186862.aspx