7

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]
ZygD
  • 22,092
  • 39
  • 79
  • 102
Jared
  • 2,443
  • 8
  • 26
  • 40
  • Are you taking RIGHT(STRID, 3) to convert A001 to 1, or to convert 1001 to 1? If the former, keep in mind that STRID will not be numeric. If the latter, consider the modulo operator (% 1000). –  Dec 02 '11 at 19:02
  • I am not an expert at SQL at all so I am not sure what a modulo operator is. I am trying to compare 190 = 190 but some of the data from my RIGHT call gets ABC = 190 – Jared Dec 02 '11 at 19:08
  • Right. I'm asking what's left of 190 in the column STRID. Is all of STRID a number? Because if not, if it can be A190, then your pseudo-code "CASE STRID WHEN TEXT" condition will trigger and you will not get 190. As for the modulo operator, it means the remainder after division. If all of STRID is a (positive) number, then the last three digits are the remainder of that number divided by 1000. –  Dec 02 '11 at 19:13
  • Oh I see. Here is what the full text looks like for 190: IDS_ENUM_Change_262147_190 and the other result: IDS_STATUS_HOLD – Jared Dec 02 '11 at 19:19
  • Then go with Conrad Frix's solution, which checks whether the last three characters are numeric, rather than the whole string, and does so without the problems of the other answers. –  Dec 02 '11 at 19:21

6 Answers6

16

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]
Community
  • 1
  • 1
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • 1
    Technically correct, but, wow, using a CASE in a join is ugly as sin, heh. – Bert Dec 02 '11 at 19:22
  • 2
    the proper way to fix this horrible design is to split out the string from the number into two columns, if that isn't possible (legacy system) it would be best to create a persisted computed column, with an index if necessary for your usage. I detail this in my answer below. – KM. Dec 02 '11 at 19:26
4

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.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • +1 for including the computed column DDL. Sadly the OP already commented that they can't modify the table. But if the OP could this is definitively the way to go. – Conrad Frix Dec 02 '11 at 19:31
  • @Conrad Frix, then a persisted indexed view.... or a new 1 to 1 table that only includes the PK and the NewIntID – KM. Dec 02 '11 at 19:43
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.

Dave
  • 860
  • 7
  • 18
  • I am not able to modify the actual SQL table. Do you mean in the query? – Jared Dec 02 '11 at 19:04
  • That won't work. All that does is generate a server error when storing STRID values that do not end in digits. –  Dec 02 '11 at 19:04
1

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • I like this answer; looking at the solution from the other perspective (converting to string); ingenious --worked for me! – Omar Aug 01 '14 at 21:48
0

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]
Jeff Hornby
  • 12,948
  • 4
  • 40
  • 61
  • `ISNUMERIC` is extremely unreliable. Try checking `SELECT ISNUMERIC('2233e4')` – JNK Dec 02 '11 at 19:14
  • @JNK what's so unreliable about that? I expect that it would recognize exponential notation – Jeff Hornby Dec 02 '11 at 19:16
  • 1
    Because if the last 3 characters of his field are `3e3` they will pass `ISNUMERIC` but fail to `JOIN` on an int and bomb. – JNK Dec 02 '11 at 19:17
0

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
Bert
  • 80,741
  • 17
  • 199
  • 164