在JOIN条件下使用RTRIM(Using RTRIM in JOIN condition)

在JOIN条件下使用RTRIM(Using RTRIM in JOIN condition)

我想通过描述将两个包含产品的表放在一起

TABLE 1: [..fields..] [DESCRIPTION1] [..fields..]

TABLE 2: [..fields..] [DESCRIPTION2] [..fields..]

SELECT [..fields..] FROM TABLE1,TABLE2 INNER JOIN TABLE1 ON TABLE1.DESCRIPTION1 = TABLE2.DESCRIPTION2

但在很多情况下，两个表中的描述以空格结尾，我也想得到它们，这就是为什么我这样做的：

SELECT [..fields..] FROM TABLE1,TABLE2 INNER JOIN TABLE1 ON TABLE1.DESCRIPTION1 = TABLE2.DESCRIPTION2 OR RTRIM(TABLE1.DESCRIPTION1) = RTRIM(TABLE2.DESCRIPTION2)

这不会产生任何错误，但不会带来更多数据。

有人能帮帮我吗？

感谢每一个帮助:)

哈利，先谢谢你

I want to put two tables which are containing Products together by their DESCRIPTIONs

TABLE 1: [..fields..] [DESCRIPTION1] [..fields..]

TABLE 2: [..fields..] [DESCRIPTION2] [..fields..]

SELECT [..fields..] FROM TABLE1,TABLE2 INNER JOIN TABLE1 ON TABLE1.DESCRIPTION1 = TABLE2.DESCRIPTION2

But in lots of cases, there are descriptions in both tables ending with an space, i also want to get them, thatswhy i tried it that way:

SELECT [..fields..] FROM TABLE1,TABLE2 INNER JOIN TABLE1 ON TABLE1.DESCRIPTION1 = TABLE2.DESCRIPTION2 OR RTRIM(TABLE1.DESCRIPTION1) = RTRIM(TABLE2.DESCRIPTION2)

Which produces no error, but does not bring more data.

Is anybody able to help me?

Every help is appreciated :)

Thanks in Advance, Harry

最满意答案

问题可能是描述末尾的CRLF字符，它看起来像SSMS中的空格，并且不会被RTRIM()删除。

要检查，看看是否选择where Description like '%' + char(13) + char(10)返回任何内容。 如果看起来在描述的末尾只有一个空格，请单独尝试char(13)或char(10) 。 最后，一个Tab（ char(9) ）也看起来像一个空格，所以你也可以试试。

如果它确实是任何这些字符，你可以使用REPLACE来摆脱它们：

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

我在这里找到了 。

The problem might be CRLF characters at the end of the Description, which will look like spaces in SSMS and won't be removed by RTRIM().

To check, see if selecting where Description like '%' + char(13) + char(10) returns anything. If it looks like there is only 1 space at the end of the Description, try either char(13) or char(10) alone. Finally, a Tab (char(9)) would also look like a space, so you could try that as well.

If it does turn out to be any of these characters, you could use REPLACE to get rid of them:

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

which I found here.