How can I use "LIKE" operator in AMPScript in LookupRows() function when querying Data Extension Records?

Question

I have following data extension: "My_DataExtension" "My_DataExtension" DE has following fields:

1. My_Text_field_1 (text 300)

I have a cloudPage named "My_CloudPage".

• This cloudPage has 1 input text element like "Text_Input_1" .
• Also, this cloudpage has "submit" button.

Now, when a user enters some text value in "Text_Input_1" & clicks "submit" button, I want to search in "My_DataExtension" for "My_Text_field_1" for the matching value entered by the user.

I was trying to use the "LookUpRows()" function and this function would take "WHERE" clause. But, user might not enter exact text.

How can I retrieve records from data extension by matching DE field values partially?

E.g. "My_DataExtension" has following 3 records: - My_Text_field_1 ==> "This is my test value 1" - My_Text_field_1 ==> "This is my test value 2" - My_Text_field_1 ==> "This is my test value 3"

And if the user has entered following value on the cloudpage & clicks "submit" button: i.e. Text_Input_1 ==> "This is"

I want to be able to search for the "My_DataExtension" records & it should return me 3 DE rows.

Please let me know which AMPscript function can help me achieve this?

Answer 1

Unfortunately there is no direct way to do this in AMPScript.

You have 2 possibilities that you can use. 1 in AMPScript and 1 in SSJS.

AMPSCRIPT: (if retrieved rows is fairly low quantity)

1. First, you would have to add a row to your DE that contains a constant value (flag) inside of it. I usually name this field 'Secret' and use a value of 1.
   
   So for instance your DE would have 2 fields - My_Text_field_1 | Secret
2. You would do your lookup based on this 'Secret' field - LookupRows('yourDE', 'Secret', 1)
   
   This will return ALL rows in this DE up to 2500 I believe.
3. You would then utilize IndexOf() function for your returned value from the Cloudpage to do a 'Contains' inside of a For Loop

Putting it all together you get:

%%[
SET @Text = AttributeValue('Text_Input_1') 
/* or RequestParameter('Text_Input_1') if the value is being passed as a parameter */
SET @Rows = LookupRows('yourDE', 'Secret', 1)
SET @Count = 0

FOR @i=1 TO Rowcount(@Rows) DO

  SET @Row = Row(@Rows, @i)
  SET @Text_Input_1 = Field(@Row, 'Text_Input_1')

  IF IndexOf(@Text_Input_1, @Text) > 0 THEN

    SET @Count = ADD(@Count, 1)

    /* Set any other actions you want to take if the value contains the string */

  ENDIF

Next @i
]%%

SSJS: (Processes slow and has a larger resource draw)

For the SSJS solution, you would use the Rows.Retrieve() Function.

<script runat="server">

Platform.Load("core", "1.1.1");

var text_Input_1 = Platform.Request.GetFormField('Text_Input_1'); /* Get form field */
var yourDE = DataExtension.Init("yourDE");
var filter = {Property:"My_Text_field_1 ",SimpleOperator:"Like",Value: text_Input_1};
var data = yourDE.Rows.Retrieve(filter);
</script>

This will return a rowset containing all of the rows that are 'like' your text input value from the cloud page.