For those that are a bit on the older side, no the title is not written in ‘Valley Speak’. I am talking about utilizing functions to achieve LIKE and IN as options inside of AMPscript where they are not native capabilities. Now what I am pushing out here is not really rocket science and likely there are many others out there who already use this – BUT it is also one of those things that only seem obvious after you know about it or have utilized it in other languages.

My goal here is to help people skip straight to the ‘A-HA’ moment and be able to utilize these functionalities in their work. Another great tidbit is that what you use here in AMPscript can also be useful if translated into SSJS.

LIKE, what is it?

LIKE is pretty much a where clause, but it allows for ‘soft matches’ instead of a hard match. What does that mean? Well it means if you utilize the appropriate wildcards (usually ‘%’) you can choose if you want the string to begin, be in the middle or end with that. Let me give an example as I am sure that is confusing.

This is matching where [myVar] starts with ‘myVar’.

[myVar] LIKE 'myVar%'

This would match on ‘myVar‘, ‘myVariable’, ‘myVarmit’

Next is matching where the declared string exists in [myVar] somewhere:

[myVar] LIKE '%myVar%'

This would match on ‘WhereismyVar‘, ‘myVariable’,’EatmyVarHar’

And finally is where [myVar] ends with the declared string.

[myVar] LIKE '%myVar'

This would match on ‘WhereismyVar’, ‘WhoismyVar‘, ‘MatchmyVar‘

Ok, that’s pretty good stuff, right? Well, the issue is this is not a native capability in AMPscript or SSJS. So, to get around this, we have to utilize the function IndexOf(). If used right, this function is perfect for a soft match of ‘exists in’ section of LIKE, but to get the other aspects, it takes a bit more to do.

LIKE That String Exists

To be able to do this and get the ‘Loose Context’ of a soft match, we honestly could just utilize the default capabilities of IndexOf(). IndexOf() will take a string and then search in that string for another string to see if it exists. It will not take into account other characters or spaces, etc. It just looks for those specific characters in that order and if it does exist, it returns the ‘index’ or number of characters into the string it had to go to find that match.

So doing the below would match [myVar] LIKE '%myVar%'

%%[
    SET @myVar = "myVar"
    SET @myString = "smyVartenance"
  
    SET @exists = IIF(IndexOf(@myString,@myVar) > 0, 1, 0)
    
    IF @exists == 1 THEN
      /* Code for if the var matches the string*/
    ENDIF
]%%

Below is an alternative I offer up as its a template for the future versions of LIKE we will go over and this way it can remain a consistent approach.

%%[
    SET @myStr1 = "Bats|Bananas|Booze|Ooze"
    SET @myVar = "nan"
  
    SET @exists = IIF(IndexOf(CONCAT("|",@myStr1,"|"),@myVar) > 0,1,0)
      
    IF @exists == 1 THEN
      /* Code for if the var matches the string*/
    ENDIF
]%%      

As a note, this is in no way a better or more performant option, if anything, it might even be slightly less performant. I just wanted to introduce this approach as it is the template for the other two versions and how to get the ‘Begins with‘ and ‘Ends with‘ LIKE.

LIKE That Begins With

Now that we handled the part that is fairly native via the IndexOf() function, we will dive into the Begins with capability. IndexOf() will always by default handle the search as a loose context/soft search. Meaning if the search string is in the larger string no matter the context, it will return the index of where it finds it. This can cause false positives at times and create some less then ideal results.

To get around this, we build a modified delimited string, like we did in my alternative above, and then use that to then manipulate the search string to include this delimited approach to ensure match is only on a specific part.

For Example:

%%[
    SET @myStr1 = "Bats|Bananas|Booze|Ooze"
    SET @myVar = "ooze"
  
    SET @exists = IIF(IndexOf(CONCAT("|",@myStr1,"|"),CONCAT("|",@myVar)) > 0,1,0)
      
    IF @exists == 1 THEN
      /* Code for if the var matches the string*/
    ENDIF
]%%

As you can see, by turning the ‘@myVar’ into a CONCAT() that adds the pipe to the front of the variable, you ensure that it will only match on any item in the string that is at the beginning.

For example, in the sample above, @Exists would return as True (1) as it would match on ‘|Ooze’ – but would ignore ‘|Booze’ because it now matches with the pipe at the front. Next we are going to dive into Ends with, which is strikingly similar to Begins with.

LIKE That Ends With

Once you have Begins with down, getting Ends with is pretty easy. You really just do the opposite with the search value. Instead of CONCAT("|",@myVar) you do CONCAT(@myVar,"|"). This then puts the delimiter showing the separation of words/phrases in the string at the end of the search string, meaning it will only match if its at the end of that word.

It still uses the same modified delimited string, etc. otherwise so once you have mastered one, you can easily pick up the other.

For Example:

%%[
    SET @myStr1 = "Bats|Bananas|Anapolis|Banana"
    SET @myVar = "Ana"
  
    SET @exists = IIF(IndexOf(CONCAT("|",@myStr1,"|"),CONCAT(@myVar,"|")) > 0,1,0)
      
    IF @exists == 1 THEN
      /* Code for if the var matches the string*/
    ENDIF
]%%

In the above, you would get the response of @exists returning True (1) because it would match on Banana. Now, you will notice it skipped over Bananas and Anapolis because although these are soft matches, they do not match with the pipe at the end. The search string is not just “ana”, but instead “ana|”. So the only one that matches is “Banana” because, with the CONCAT() in the IndexOf(), it is actually “Banana|”.

Sweet. That is, like, totally how to use LIKE in AMPscript! Next up is IN.

Batteries not INcluded

Same as LIKE, there is no native way to do this. You can use IndexOf() natively for a soft search of IN, but that can lead to false positives or other issues . So what do we do? First, let’s see what the heck IN means.

IN is a native SQL function, much like LIKE, which looks for a specified string inside a delimited string of options. Essentially IN is a shorthand version of X = Y OR X = Z OR X = A Which would instead be X IN (Y,Z,A). This is pretty useful for those long OR statements, right? Saves a ton of space and makes maintenance SO much easier.

The big question now is…how do we do this?

How do we do this?

The answer is surprisingly simple and extremely closely related to the way we do LIKE. We utilize IndexOf() along with CONCAT() and the modified pipe delimited string. The main difference is that in order to use this, we will need to put a pipe on BOTH sides of the string you are using to search through. This forces only exact matches to be recognized and does not allow for any soft matches at all.

For Example:

%%[
    SET @myStr1 = "Bats|Bananas|Anapolis|Banana"
    SET @myVar = "Banana"
  
    SET @exists = IIF(IndexOf(CONCAT("|",@myStr1,"|"),CONCAT("|",@myVar,"|")) > 0,1,0)
      
    IF @exists == 1 THEN
      /* Code for if the var matches the string*/
    ENDIF
]%%

In the example above, it would return true, but it would return true on Banana at the end of the delimited string, not on Bananas which was earlier. This is because the value we are using to search is now “|Banana|” which does not match “|Bananas|”.

Now, some of you may be asking, couldn’t we just have a delimited string and use BuildRowsetFromString and iterate through that? Yes, you certainly can. But I have found this method to be easier, less code and more performant. This method only requires a single statement utilizing IndexOf() instead of requiring a rowset build, a for loop and then a comparison. I find this much cleaner and much less of a processing hog. That does not mean one is right and the other is wrong. As with all things in SFMC, what is wrong in one place is the best choice somewhere else.

You may be thinking, ok. I must be done now, right? There is no way there is anything more with IN that can be done….right? WELL I have a surprise for you! How about looking into doing an IN with not just one delimited string, but TWO delimited strings. (For this example, I will begin referencing the delimited strings as arrays, as essentially we will be using them as such)

IN Two Arrays

Yes, AMPscript does not allow for arrays. So how can we use arrays in AMPscript if they are not a native capability? The delimited string! Yes, a delimited string is essentially a Stringified version of an array. And from that delimited string, you can use the BuildRowsetFromString, to then be able to iterate through it and utilize each as individual values.

This is very important as it is paramount to how to do IN via 2 arrays/delimited strings. So in order to do this, you would need a combination of BuildRowsetFromString(), IndexOf(), CONCAT() and FOR loop and iteration of rows in a RowSet. As you can tell, this is a bit more complex than the above, but it also is a much more complex use case.

Before going deep into HOW to do this, let’s go over WHAT exactly THIS is.

What do you get?

When doing an IN on two arrays, what does that mean? What do you get returned? Well, unlike the previous LIKE and IN, we can have more than one match as its multiple values being considered on both strings. This means we can get more info than just ‘does this exist’. We can also build a delimited string returning the matching values between each string. So if there are two matches across the arrays, we would get val1|val2 as a returned string. Super helpful, right?

Now that we know what we will get, lets dive into the HOW:

%%[
  SET @myStr1 = "Bats|Bananas|Booze|Ooze"
  SET @myStr2 = "Ooze|Blood|Bats"
  SET @myArr = BuildRowsetFromString(@myStr1,"|")
  SET @match = 0

  FOR @i=1 TO Rowcount(@myArr) DO
    SET @row = Row(@myArr,@i)
    SET @val = FIELD(@row,1)

    IF NOT EMPTY(@val) AND IndexOf(CONCAT("|",@myStr2,"|"),CONCAT("|",@val,"|")) > 0 THEN
     SET @match = 1 
     SET @resStr = IIF(EMPTY(@resStr), @val,CONCAT(@resStr,"|",@val))
    ENDIF

  NEXT @i
]%%

As you can see, its not as simple and clean as it was with the previous versions. Because of this, I am gonna break it down a bit more to help explain what is happening here.

First we start with the two delimited strings being declared at the top. These are the two arrays we will be comparing.

We then set the rowset for the first string utilizing pipe as the delimiter. We also set the match variable to 0.

Next we utilize a FOR loop to iterate through each value of the rowset we made (@myArr)

Inside this for loop, we utilize ROW() and FIELD() to collect the individual values of the rowset

We then use an IF statement to ensure that there is a value in our @val variable and then an INDEXOF() similar to the others we had.

Before moving beyond this, let’s dive deeper into this INDEXOF(). Essentially what this is doing is taking your second array and turning it into that modified delimited string I used in the other examples above. Then we are also doing a ‘hard match’ like we did in the IN by putting pipes on both sides of the search phrase. Essentially this then becomes an iterative version of the IN capability we had – pulling each value from array1 and comparing it to all the values in array2 to see if there are matches.

Then, if there is a match, we turn @match from 0 (False) to 1 (True) and then we set @resStr with the value. We also utilize an IIF() on @resStr to make it a delimited string if there are more than one matches.

Now you also could include a ‘match counter’ variable in there to count the total number of matches or a few other things, but that is up to you!

As always feel free to leave any comments or questions and I will try to get to them as soon as I can. Thanks for reading!