当前位置：文江博客话题详情

修剪临时表中的所有列并选择数据

发布于 2024-12-02 15:35:38 字数 870 浏览 3 评论 0原文

有一个简短的问题。

我首先选择一个数据集到临时表中。

临时表的列中包含的数据包含额外的空格。许多列都带有额外的空格。因此，我用谷歌搜索检查如何删除列中的所有空格。

找到了这个解决方案 SQL Server：如何对所有列执行 Rtrim表的 varchar 列

但是，我尝试对临时表实现相同的功能，但它不能按原样工作，因为临时表是在不同的架构中创建的。

所以，我修改了相同的内容并尝试了。但没有运气。有人可以指出我哪里做错了吗？

代码如下：

DECLARE @Op NVARCHAR(1000)
DECLARE @table_name AS VARCHAR(300) 

SET @table_name = (SELECT TOP 1 [name] FROM tempdb..sysobjects WHERE name LIKE '#tempFinalResults%')

SET @Op = ' UPDATE ' + @table_name + ' SET COLUMN_NAME = LTRIM(RTRIM(COLUMN_NAME)) FROM tempDB.information_Schema.Columns WHERE DATA_Type IN (''varchar'', ''char'', ''nchar'', ''nvarchar'')'

Exec sp_executesql @Op

Select * from #tempFinalResults

谢谢大家。欣赏它。

谢谢，肖恩

原文

Got a quick question.

I first select a dataset INTO a temp table.

The data contained in the columns of the temp table, contains extra spaces in them. Many columns exist with extra spaces. So, I googled to check how to remove all spaces in the columns.

Found this solution SQL Server: How to perform Rtrim on all varchar columns of a table

But, I tried to implement the same for the temp table, but it does not work as it is, as the temp tables are created in a different schema.

So, I modified the same and tried. But no luck.
Can someone point me, where I am doing it wrong?

The code is as follows:

DECLARE @Op NVARCHAR(1000)
DECLARE @table_name AS VARCHAR(300) 

SET @table_name = (SELECT TOP 1 [name] FROM tempdb..sysobjects WHERE name LIKE '#tempFinalResults%')

SET @Op = ' UPDATE ' + @table_name + ' SET COLUMN_NAME = LTRIM(RTRIM(COLUMN_NAME)) FROM tempDB.information_Schema.Columns WHERE DATA_Type IN (''varchar'', ''char'', ''nchar'', ''nvarchar'')'

Exec sp_executesql @Op

Select * from #tempFinalResults

Thanks guys. Appreciate it.

thanks,
Shawn

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

酷炫老祖宗 2024-12-09 15:35:38

如果您知道需要修剪哪些列，则可以将它们作为选择的一部分修剪到临时表中，如下所示

SELECT 
ID,
RTRIM(myCharColumn1) as myColumn1,
RTRIM(myCharColumn2) as myColumn2,
RTRIM(myCharColumn3) as myColumn3
INTO #myTempTable
WHERE.....

If you know which columns need to be trimmed, you could trim them as part of your select into the temp table, as in

SELECT 
ID,
RTRIM(myCharColumn1) as myColumn1,
RTRIM(myCharColumn2) as myColumn2,
RTRIM(myCharColumn3) as myColumn3
INTO #myTempTable
WHERE.....

回复收藏 0 原文

你如我软肋 2024-12-09 15:35:38

您的问题是，您必须使用 SELECT 语句构建列名称列表，以便它循环遍历所有列。

我认为这可能有效：

DECLARE @Op NVARCHAR(3000)
DECLARE @table_name AS VARCHAR(300) 

SET @table_name = (SELECT TOP 1 [name] FROM tempdb..sysobjects WHERE name LIKE '#tempFinalResults%')

SELECT @Op = COALESCE(@Op + ',[', '[') + COLUMN_NAME +
    '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))' 
FROM 
    tempDB.information_Schema.Columns 
WHERE 
    DATA_Type IN ('varchar', 'char', 'nchar', 'nvarchar')

SET @Op = 'UPDATE [' + @table_name + '] SET ' + @Op

Exec sp_executesql @Op

Your problem is that you have to build the list of column names with a SELECT statement so that it loops through all columns.

I think this might work:

DECLARE @Op NVARCHAR(3000)
DECLARE @table_name AS VARCHAR(300) 

SET @table_name = (SELECT TOP 1 [name] FROM tempdb..sysobjects WHERE name LIKE '#tempFinalResults%')

SELECT @Op = COALESCE(@Op + ',[', '[') + COLUMN_NAME +
    '] = LTRIM(RTRIM([' + COLUMN_NAME + ']))' 
FROM 
    tempDB.information_Schema.Columns 
WHERE 
    DATA_Type IN ('varchar', 'char', 'nchar', 'nvarchar')

SET @Op = 'UPDATE [' + @table_name + '] SET ' + @Op

Exec sp_executesql @Op

回复收藏 0 原文

~没有更多了~