JOIN ON 子句中的 T-SQL Case 语句 [英] T-SQL Case Statement in a JOIN ON Clause

问题描述

我正在尝试在 JOIN ON 子句中构建 case/if 语句.

I am trying to build a case/if statement in a JOIN ON clause.

LEFT JOIN [CTSTRC] [Statuses] ON RIGHT([Statuses].[STRID], 3) = [CTE].[F61]

问题是 [Statuses].[STRID] 列包含文本和数字.我将它与 [CTE].[F61] 进行比较的列是一个整数.

The problem is that the column [Statuses].[STRID] contains text and numbers. The column I am comparing it to [CTE].[F61] is an integer.

有没有办法检测列 [Statuses].[STRID] 是否有字符或数字，如果是字符，则将其设置为 0?

Is there a way to detect if column [Statuses].[STRID] has a character or a number and THEN set it to 0 if it is a character?

这是一个伪查询来帮助:

Here is a pseudo query to help:

LEFT JOIN [CTSTRC] [Statuses] 
    ON RIGHT((
        CASE [Statuses].[STRID] 
            WHEN TEXT THEN 0 
            ELSE CAST([Statuses].[STRID] AS INT) END), 3) = [CTE].[F61]

推荐答案

您正在寻找 IsNumeric 但它并不总是有效(+、- 和 . 是数字)，因此您需要使用解决方案 由 GBN 描述 将 .0e0 添加到您的 varchar

You're looking for IsNumeric but it doesn't always work (+,- and . are numeric) so you need to use the solution described by GBN which is to add .0e0 to your varchar

LEFT JOIN [CTSTRC] [Statuses] ON 
    (CASE WHEN ISNUMERIC(RIGHT([Statuses].[STRID], 3) + '.0e0) = 1 
          THEN  CAST(RIGHT([Statuses].[STRID], 3) AS INT) 
          ELSE 0  END) = [CTE].[F61] 

这篇关于JOIN ON 子句中的 T-SQL Case 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！