How to write a function that returns positive if function contains values from another column?

ljb18

The title is a little confusing, but basically I want to write a function like this:

CASE
WHEN `Track Split` LIKE '%AZ%' THEN 'AZ'

end

EXCEPT, instead of basing it off of a string, I want it to pull values from another column. I have a lot of changing data from row to row, so I want the row to check to see if the data contains a specific value from a separate column so I don't have to write a ton of different strings. Something like:

CASE
WHEN `Track Split` LIKE %`Library`% THEN 'AZ'

But that's clearly not working. Does anyone have any suggestions?

GrantSmith

Try something like this:

CASE WHEN `Track Split` LIKE CONCAT(CONCAT('%', `Library`), '%') THEN 'AZ' END

MichelleH

@ljb18 Since you're trying to determine whether the value of the Library column exists within the Track Split column, you could also try using the INSTR function to return the position of the Library value. If there is a match then the position will be greater than 0.

case 
  when INSTR(`Track Split`,`Library`) > 0 then 'AZ'
end

GrantSmith

Try something like this:

CASE WHEN `Track Split` LIKE CONCAT(CONCAT('%', `Library`), '%') THEN 'AZ' END

MichelleH

@ljb18 Since you're trying to determine whether the value of the Library column exists within the Track Split column, you could also try using the INSTR function to return the position of the Library value. If there is a match then the position will be greater than 0.

case 
  when INSTR(`Track Split`,`Library`) > 0 then 'AZ'
end

ljb18

That worked perfectly, thank you so much!

ashokmuthukumar

It works. It does my need. It is a powerful function. I used it for join