Sql server rtrim 对我不起作用，建议? [英] Sql server rtrim not working for me, suggestions?

问题描述

我有点惊讶我找不到快速解决我所面临的问题的方法，这似乎是一件很常见的事情.我无法删除选择查询中的尾随空格.我想将数据放入一个 csv 文件中.如果这更容易，我很乐意复制/粘贴来自 SSMS结果到文本"的结果.无论哪种方式，我的查询是:

I'm a bit surprised that I can't find a quick solution to what I'm up against, seems like it'd be a common thing to deal with. I can't get rid of the trailing spaces in my select query. I'd like to get the data into a csv file. I'm happy to copy/ paste the results from SSMS "results to text" if that's easier. Either way, my query is:

declare @maxDate date = (select MAX(TradeDate) from tblDailyPricingAndVol)

select p.Symbol, ','
from tblDailyPricingAndVol p
where p.Volume > 1000000 and p.Clse <= 40 and p.TradeDate = @maxDate
order by p.Symbol

它返回:

A       ,
AA      ,
ABB     ,

等等.在 p.Symbol 字段周围进行 Rtrim 没有帮助.如果我能找出最佳解决方案，我会得到以下结果:

etc. Rtrim around the p.Symbol field didn't help. If I could figure out the best solution, I'd have results of:

A,AA,ABB

等等.有接班人吗?一如既往地提前致谢..

and so on. Any takers? Thanks in advance as always..

推荐答案

从您的预期结果来看，您似乎想将所有行中的 symbol 值连接成一个逗号分隔字符串.

From your expected result, it seems that you want to concatenate the symbol value from all the rows into one comma demimited string.

为了做到这一点，你可以这样做:-

In order to do that, you can do this :-

DECLARE @str VARCHAR(MAX)
SELECT @str = COALESCE(@str+',' ,'') + LTRIM(RTRIM((p.Symbol)))
FROM tblDailyPricingAndVol p
WHERE p.Volume > 1000000 and p.Clse <= 40 and p.TradeDate = @maxDate
ORDER by p.Symbol
SELECT @str
GO

其他替代方法也可以参考这里

Additional alternate methods can also be referenced here

关于 RTRIM 不工作的问题，这看起来很奇怪.为了安全起见，我在上面的查询中添加了 LTRIM + RTRIM.但是，只要您的 symbol 列是某种 varchar，RTRIM 就没有理由不工作.

Regarding your issue with the RTRIM not working, that seems very odd. To be on the safe side, i have added LTRIM + RTRIM to the above query. However, as long as your symbol column is some kind of varchar, there is really no reason for the RTRIM to not work.

您能否说明表中symbol 列的数据类型?

Can you clarify the datatype of symbol column in your table?

这篇关于Sql server rtrim 对我不起作用，建议?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！