Skip to content
Advertisement

Optimal join from a column to the concatenation of those columns?

I have a table TableLHS with a column ObjInfo:

which I would like to join to TableRHS, with columns:

So the join here involves 1) dropping the leading zeroes from ObjNumber, and 2) concatenating the three columns in TableRHS together.

My best shot at the join is:

My current performance could use some improvement. Is there a smarter way of tackling this?

e.g. Is there a more effective way of getting past the leading zeroes in ObjNum? Would it be faster to work the other way round, and try to split ObjInfo on the slashes? Would performing this operation on every row nullify the effect of an index on ObjNum?

Advertisement

Answer

One way to get around the “integers” with leading zeroes would be to do a formal cast to the integer type, then cast back to varchar to use in building the path expression for the join.

screen capture from demo link below

Demo

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement