0

I want to loop through a comma separated list of numbers to see which is present in a string, here is a very simplified version of what I want to do:

<script runat="server" language="ampscript">

VAR @string, @numbers
SET @string = "munksgaard#13"
SET @numbers = "11,12,13,14"

IF IndexOf(@string, "11") != 0 THEN
SET @numberfound = "11"
ELSEIF IndexOf(@string, "12") != 0 THEN
SET @numberfound = "12"
ELSEIF IndexOf(@string, "13") != 0 THEN
SET @numberfound = "13"
ELSEIF IndexOf(@string, "14") != 0 THEN
SET @numberfound = "14"
ELSE
SET @numberfound = "notfound"
ENDIF

</script>

@string: %%=v(@string)=%%<br>
@numbers: %%=v(@numbers)=%%<br>
@numberfound: %%=v(@numberfound)=%%<br>

So, in this example I find the number 13 present in the string.

First of all, I'm not a programmer, so I am aware that my AMPScript is not very good, especially my IF ELSE approach as it isn't easily scalable.

Second of all, the above is just an example. When I need to finish this project I'd have to handle much more complex strings and search for many more numbers, I would have a string and a comma separated list of numbers like this:

**SET @string = "586136#outlet;586476#outlet-dametøj;623741#forår/sommer;586802#bluser, toppe & t-shirts;"**

**SET @numbers = "601292,601443,601441,601440,601444,619575,601442,632938,629574,614516,620205,587712,587649,586476,586477,586478"**

Can anyone help me with AMPScript for a proper approach on how to loop through a list of numbers in a string like the one above?

Thank you

0
3

I'm not sure what you intend to use the returned numbers for, but the following code will:

  1. Build a rowset from a delimited array of numbers
  2. Loop through the rowset and for each number in the rowset, search for that number in the string
  3. Output a list of matching numbers from the string

Code

%%[

var @string, @numbers, @counter, @rows, @rowCount, @row, @numberToFind, @findNumber, @numberFound, @delimiter, @result

set @string = '586136#outlet;586476#outlet-dametøj;623741#forår/sommer;586802#bluser, toppe & t-shirts;620205#test;586477#testagain'
set @numbers = '601292,601443,601441,601440,601444,619575,601442,632938,629574,614516,620205,587712,587649,586476,586477,586478'

set @rows = BuildRowsetFromString(@numbers,',')

set @rowCount = RowCount(@rows)

for @counter = 1 to @rowCount do

    set @row = Row(@rows, @counter)

    set @numberToFind = Field(@row,1)


    set @findNumber = IndexOf(@string, @numberToFind)

    if @findNumber > 0 then
       set @numberFound = @numberToFind 
       set @delimiter = '<br>'
    else
       set @numberFound = ''
       set @delimiter = ''
    endif

    set @result = Concat(@numberFound, @delimiter)

]%%

%%=v(@result)=%%

%%[ next ]%%

Result

620205
586476
586477
4
  • I would worry for smaller digit numbers being included in larger digit numbers. For instance using IndexOf(@string, '456') on value of '60456' would return a positive result even though it is a different number. If the numbers would always be the same number of digits, then this is probably the simplest and best solution - but with varying amounts of digits, this opens risk to false positives. – Gortonington Jul 25 '18 at 12:28
  • Thank you Elliot, great answer. I omitted parts of your answer ie. the else part of the if sentence, the delimiter var setting in the if part and the setting of the result variable as I just needed the numberFound var. In this project only one of the numbers in the numbers var will have a match. Only thing left for me to find out is how to build the numbers var from a data extension in stead of having a static variable inside my AMPScript :) – Felix Sanchez Jul 25 '18 at 12:55
  • @FelixSanchez - to grab from a DE, use a lookup: LOOKUP('yourNumbersDE', 'Numbers', 'PrimaryKey', @PrimaryKey). This is assuming the DE has a primary key identifier. Such as Set 1 | 60545,60565,60566 etc.. Where 'Set 1' would be the key. – Gortonington Jul 25 '18 at 15:13
  • 1
    Hi @Gortonington, I agree this approach won’t fit every use case. I had the make assumptions here and you are right, for the benefit of others it’s important to note that you might need to change the search pattern used by IndexOf or use the RegExMatch function. – Eliot Harper Jul 25 '18 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.