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

Chris p2 · ‎09-24-2019

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)

Michael Ritchie · ‎09-25-2019

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
}

View solution in original post

AbdulAzeez · ‎09-24-2019

You can something like this:

So here i am searching the name which contains Email which should exclude the Reminder

In the script use as:

nameLIKEemail^nameNOT LIKEreminder

Matt Taylor - G · ‎09-24-2019

There is an ENDSWITH operator that you could use to find all the records which you don't want and then explicitly exclude them from your subsequent query. sort of like this:

var array = [];

var gr = new GlideRecord('table');

gr.addEncodedQuery('field_nameENDSWITH123');

gr.query();

while (gr.next()) {

array.push(gr.getUniqueID());

}

var sysIDs = "sys_id!="+array;

sysIDs =sysIDs .replace(/,/g,"^sys_id!=");

gr.initialize();

gr.addEncodedQuery('fieldCONTAINS123^' + sysIDs);

gr.query();

while (gr.next()) {

//iterate on the records as necessary

}

Chris p2 · ‎09-24-2019

Hi, that is close to what i need but would exclude the records where as what i need to do is ignore the last number

There may be an instance where it ends in 003 but also starts with 003, is still want this record

BOB123O123

Matt Taylor - G · ‎09-25-2019

No worries. To achieve the objective you'll need to do some string ops to make sure the first occurance of 123 is not at the very end of the string of the value value stored in the field. Then you can store the sys_id's of those records and iterate through them with a for loop or however you wish to use them.

var array = [];

var gr = new GlideRecord('table');

gr.addEncodedQuery('field_nameCONTAINS123');

gr.query();

while (gr.next()) {

var fieldValue = gr.field_name + '';

if (fieldValue.indexOf('123') != (fieldValue.length - 3)) {

array.push(gr.getUniqueID());

}