Glide query, how to i search on CONTAINS but exclude the last 3 characters

[Chris p2]

Hi, i want to glide query within SNOW to find a field that contains X but exclude the last 3 characters

EG

Field_Name

Fred123O909

BOB123O857

Jim475O123

Jones589O909

i can only use the number '123' and I want to return Fred and Bob only. My query at the moment would return Fred, Bob and Jim

WHERE CONTAINS 123

Where as i want to do something like WHERE CONTAINS (LEFT -3, 123)

[Sushant Kadam1]

Hi,

Can you please try with below query:

numberLIKE123^numberNOT ENDSWITH123

[Chris p2]

Hi, unfortunately that would exclude numbers that end in XX rather than ignore

for example, if i had a number BOB123O123 it wouldn't return but should

[Michael Ritchie]

I would just perform a query for all records that contain 123, then iterate through them all filtering out the ones you don't want while pushing the ones you do want to an array.  Then perform the query again with just the ones you want.  I don't see any other option to be honest.

[Michael Ritchie · Accepted solution]

Something like the following.  I took the liberty to use the sys_user's name as an example:

var queryString = "123";

var userList = [];
var user = new GlideRecord("sys_user");
user.addQuery("name", "CONTAINS", queryString);
user.query();
while (user.next()) {
	// make sure name does NOT end with queryString value
	if (!user.name.toString().endsWith(queryString)) {
		userList.push(user.getValue("sys_id");
	}
}

user.initialize();
user.addQuery("syd_id", "IN", userList.toString());
user.query();
while (user.next()) {
	//do whatever you need to with these records
}