inner join - How do I ignore non numeric values for joined field in SQL Server? -

select * table1 t1 inner join table2 t2 on t1.id = t2.id inner join table3 t3 on           case when charindex(':', t3.reference_number, 3) > 3                substring(t3.reference_number, 3, charindex(':', t3.reference_number, 3) -3 )                else right(t3.reference_number, len(t3.reference_number) -2) end = cast(t2.purchaseorderid int) 

i have query above want join on table value want join on substring of field itself. field contains non-numeric values don't want inner join on field if that's case.

how can that?

*t3.reference_number field in format of o:###### when want inner join can text other time. know terrible database design it's legacy system/db can't change right now.

---

for example:

t3.reference_number = "o:345215" 345215 foreign key table want join on

but if t3.reference_number = "hello" don't want join because have sql error due not being able convert substring int because it's not numeric.

here go... put entire case in isnumeric() function , presto.

declare @table1 table (reference_number varchar(25)) declare @table2 table (purchaseorderid int)  insert @table1 (reference_number) select 'o:345215' union select 'hello'  insert @table2 select '345215'  select * @table1 t1 join @table2 t2    on case when charindex(':', t1.reference_number, 3) > 3                substring(t1.reference_number, 3, charindex(':', t1.reference_number, 3) -3 )                else right(t1.reference_number, len(t1.reference_number) -2) end = cast(t2.purchaseorderid int)    , isnumeric(case when charindex(':', t1.reference_number, 3) > 3                substring(t1.reference_number, 3, charindex(':', t1.reference_number, 3) -3 )                else right(t1.reference_number, len(t1.reference_number) -2) end) = 1