T-SQL Case Statement in a JOIN ON Clause

问题:

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]

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

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]

Can someone point me in the right direction?

Thanks!

回答1:

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] 

回答2:

create a persisted computed column and add an index on it.

ALTER TABLE YourTable ADD
    NewIntID AS (CASE ISNUMERIC(RIGHT([Statuses].[STRID], 3) + '.0e0)
                     WHEN 1 THEN CAST(RIGHT([Statuses].[STRID], 3) AS INT) 
                     ELSE 0
                 END) PERSISTED
GO

CREATE INDEX IX_YourTable_NewIntID 
ON YourTable (NewIntID ); 
GO

you can now just join to the new NewIntID column as if it were the proper numeric ID now.

回答3:

You can try to create an expression column in the Statuses table that converts the right 3 characters to a number and then try to join on the expression column.

回答4:

Wouldn't something like this work:

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

All you really care about is whether you have a match, so why not convert the numeric to varchar? You would have to test both ideas to see which one is faster.

I do agree with @KM, fixing a bad design like this is the best solution. Having functions and Case statements in a join is an indicatior that your design is fatally flawed and should be fixed.

回答5:

You're looking for the ISNUMERIC function (I believe it was introduced in SQL 2005):

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

回答6:

You might want to try something like this

select ...
from CTE
inner join
(
    select ...
    from [Statuses]
    where ISNUMERIC(STRID + '.0e0') = 1
) rsNumeric on CTE.F61 = rsNumeric.STRID