What is the syntax to add a "if contains the following" in my logic

arzconci

Hello. I want to create a logic that replace those rows that don't have both AP and MA in the name as # and keep untouched those that have.

AP is always in the 1st and 2nd respectively. Then MA is in the 5th and 6th. My logic seems to be not working. Can anyone help?

---------------

(Case

WHEN `sap shipment 1 tariff id` <> '%AP%MA%' THEN '#'

ELSE `sap shipment 1 tariff id`

END)

GrantSmith

Use the LIKE command instead of <>

MarkSnodgrass

It would be best to use the LEFT and SUBSTRING functions to look for those characters since they are always in the same spot. I would write the statement like this:

CASE WHEN LEFT(`shipmenttariffid`,2) = 'AP' AND SUBSTRING(`shipmentariffid`,5,2) = 'MA' 
THEN `shipmenttariffid`
ELSE '#'
END

In my case statement, I am looking for AP in the characters 1 and 2 and MA in characters 5 and 6. If I find them, then use the tariff id, otherwise, replace with the #.

GrantSmith

Use the LIKE command instead of <>

MarkSnodgrass

It would be best to use the LEFT and SUBSTRING functions to look for those characters since they are always in the same spot. I would write the statement like this:

CASE WHEN LEFT(`shipmenttariffid`,2) = 'AP' AND SUBSTRING(`shipmentariffid`,5,2) = 'MA' 
THEN `shipmenttariffid`
ELSE '#'
END

In my case statement, I am looking for AP in the characters 1 and 2 and MA in characters 5 and 6. If I find them, then use the tariff id, otherwise, replace with the #.

arzconci

Hello @MarkSnodgrass and @GrantSmith

Thank you for your feedback. Tried both and was able to make it work.

First time to try "Like" also so thanks for the knowledge share.