Comparing two substrings during query

[jasonSDBIT]

In our system we have an SSN field (###-##-####) and a Masked SSN (XXX-XX-####) field. Is there any way to retrieve a list using these two fields that shows all the records where the last 4 digits of these two fields don't match?

For example say we have this set of data:

Record 1: SSN = 123-45-6789 and Masked SSN = XXX-XX-6789

Record 2: SSN = 123-45-6788 and Masked SSN = XXX-XX-6780

Record 3: SSN = 123-45-6787 and Masked SSN = XXX-XX-6787

The query should only return record 2.

[Community Alums]

Hey there,

You should be able to use a script like below. I wrote this using the number on the incident table, but you should be able to swap out the table name in the script and the field to your table and field to get what you need:

var last_fours = {};

var records = new GlideRecord('incident');
records.query();

while(records.next()){
	var lastfour = records.getValue('number');
	lastfour = lastfour.slice(-4);

	// check if we have captured a record with this last 4 yet
	if(!last_fours[lastfour]){
		// we haven't, so add it
		last_fours[lastfour] = 1;
	} else {
		// it was already there, so increment
		last_fours[lastfour]++;
	}
}

// get rid of the items that are not unique (deletes only the data in our object in this script, not anything in the table)
Object.keys(last_fours).forEach(function(k){
    if(last_fours[k] != 1) delete last_fours[k];
});

// print out the result
gs.info(JSON.stringify(last_fours,false,4));

Hope this helps!

~Nick

[Tai Vu]

Hi @jasonSDBIT 

You can give my script a try to retrieve records having different value between 2 fields.

var result = [];
var gr = new GlideRecord('<your_table>'); //replace your table name
gr.query();
while(gr.next()){
	var ssn = gr.<ssn>.slice(-4); //replace your ssn field name
	var maskedSSN = gr.<masked_ssn>.slice(-4); //replace your masked_ssn field name
	if(ssn != maskedSSN){
		result.push(gr.getUniqueValue()); //collect records sys_id
	}
}
gs.info(result);

Cheers,

Tai Vu