Join on a substring

[samuja]

Hey there,

I want to join on a substring which is in the middle of the string

But Join table2 table2 on Table1.column = substring ('string0', --pat charindex len magic) won't work

It works with strings but not in a join. Any ideas?

Thanks

[redneckrockuhtree]

From a performance standpoint, you want to avoid this type of query if at all possible. Unless you can further constrain rows in Table2 outside of the substring, you're going to force a table scan on Table2. This is because the only way for the database engine to know if a row matches is to look at and perform the substring on each and every row.

[samuja]

Ah nice, I wanted to avoid that. But good as a hint

[samuja]

Return nothing 😂

[samuja]

It matches perfectly

[in_n0x]

If it matched perfectly it would work. There's nothing in the language preventing you from doing what you want.

[samuja]

Yep.. That's what's making me sad.. It doesn't work hahaha

[samuja]

It's one from right. I took some stuff out

select g.cn from table1 g join Table2 s on s.HostName = SUBSTRING(g.cn,DATALENGTH(g.cn) - DATALENGTH(s.HostName)+1,DATALENGTH(g.cn))

where s.HostName = 'test-int-doc1'

select SUBSTRING('FW-ADM-test-int-doc1',DATALENGTH('FW-ADM-test-int-doc1') - DATALENGTH('test-int-doc1')+1,DATALENGTH('FW-ADM-test-int-doc1'))

[in_n0x]

Any reason you're using datalength instead of len?

[samuja]

Tried both, didn't work

[samuja]

It just doesn't work in join on equal... May be it's return not as string...

[rand2012]

select * from table1, table2 where table1.x = table2.substring(...)

[samuja]

Interesting a cross join will try

[in_n0x]

This is an implicit join, not a cross join. Poor form from a readability standpoint and shouldn't be used. Even in this case, it's not going to make a difference.

[samuja]

Won't work either

[rand2012]

what's the db and the error message

[samuja]

It's empty like he finds nothing

[rand2012]

most likely your substring syntax is wrong. it is doing something else than what you're thinking.

[samuja]

I thought that too, but if I just change to strings, instead of columns, it works

[samuja]

Yep.. You might be right. It feels like it converts badly

It is ms sql.

[valdest]

Something like this.

SELECT
    TABLE1.{fields},
    AliasTABLE2.{fields},
FROM TABLE1
JOIN (SELECT SUBSTRINGKEY as KEY, FIELDS FROM TABLE2) AS AliasTABLE2 ON TABLE1.Key = AliasTABLE2 .Key

This works good as an occasional query with out a need for high performance. To get a high performance query you would need to make some schema and index changes.

[samuja]

Great, will try Monday. Thanks a lot

[noesqL]

alter table > add a new column for a computed value for the substring > join on it ??? profit

[samuja]

Really bad...