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