Skip to content
Advertisement

Field contains or is equal to another field

I am trying to write a query to return results where “Column B” is equal to or contains “Column A”.

I need both, because some fields in Column B are an exact match for field in Column A, and sometimes the value in Column B is the value from Column A with additional characters added on the end.

Example Column A Column B
1 1234 12345abc
2 abcde abcde

I have tried:

TableName.[Column B] like TableName.[Column A]    -- this only returns exact matches
TableName.[Column B] like 'TableName.[Column A]%%' -- this returns nothing
TableName.[Column B] = 'TableName.[Column A]%%'    -- this returns nothing

Any assistance would be incredible, thanks!

Advertisement

Answer

@Rafalon is correct. Use:

TableName.[Column B] like '%%' + TableName.[Column A] + '%%'

When you add the wildcards, you have to make them a string but the TableName.[Column A] cannot be a string. The above code will include all instances of B that contains A. When you ask for an exact match, the same code will do that because when they match exactly, of course B will contain A.

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