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

ljb18
ljb18 Member
in Beast Mode

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?

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Try something like this:

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MichelleH
    MichelleH Coach
    Answer ✓

    @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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Try something like this:

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    ljb18 Member

    That worked perfectly, thank you so much!

  • ashokmuthukumar
    ashokmuthukumar Member

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

Categories