Get record from SP List which string value CONTAINS querried string?

Forms
1

Hello,

as far as I have checked, to querry SP List which string value CONTAINS specific string i should use filter of type substringof(), but i am not able to construct the querry correctly...

Target list id: "123"
Column name with string value which i want to querry: "TargetColumn"
String value which i am searching for: "MyString"
Columns i want to select: "ID, TargetColumn"

I am using:

const response = await pnp.sp.web.lists
.getById("123")
.items.select("ID, TargetColumn")
.filter("substringof('TargetColumn','MyString')")
.get();

What am I doing wrong, and how should look like the correct code?
Best regards.

2

Hello @szymanskicode,

The correct syntax for using substringof() expression is:

const response = await pnp.sp.web.lists
.getById("123")
.items.select("ID, TargetColumn")
.filter("substringof('MyString', TargetColumn)")
.get();
3

Thank you very much for the response, its correct.

But i still had a problem, with SP error. Chat GPT has helped me informing that using substringof() on Note Field (Multiline Text Field) is not supported, only Text field is working.

All the best for You!

4

@szymanskicode,

That is correct, Multiline text field cannot be used in a filter expressions.

You can filter items like so:

const response = await pnp.sp.web.lists.getById("123")
    .items.select("ID, TargetColumn")
    .get().then(function(items) {
        var filtered = []
        items.forEach(function(i) {
            if (i.TargetColumn&& i.TargetColumn.includes('MyString')) {
                filtered.push(i)
            }
        })
        return filtered;
    })