Querying on length of data

[johnsgp]

I would like a query to find those rows in a database table where the length of the data in the field meets a certain condition.   For example, I want all users where the length of the phone number is greater than 15 - this could indicate a data entry problem, for example.

How can this be done using a GlideRecord?

(I have a similar question regarding doing such queries on a List view in the UI.)

[marcelo_moreli]

Were you able to do it using GlideRecord?

[johnsgp]

I could not do the where clause in GlideRecord query.   As other posters have suggested, if you use a GlideRecord on the main table, you have to use JavaScript in a while loop and look through the whole table.

But there is another way. You can first create a Database View with the where clause.   The down side of Database Views is that a database view on an extended table (e.g. incident) does not address custom fields added only to the extension by their column names but instead by an internal column name such as a_str_5.   For sys_user which I was interested in, it worked fine.   Once I had the Database View I could make a GlideRecord query with the view instead of the whole table.

It's just a pity you need a whole new view just to do this kind of where clause.

[Sai_Dilip]

How to do this in GlideAggregate?

[ghsrikanth]

Usually to prevent these data issues, it is very much advisable to use Data Policy - preventing the insert of this kind of problematic data in the first place.

In this way, you can control any kind of insert into the table. Its the best practice too instead of doing a fix script later.

As you want to clean up the existing data, you can write a fix script for the same:

var grUser = new GlideRecord('sys_user');

grUser.query();

while(grUser.next()){

        if(grUser.getValue('mobile').toString().length > 15) // you can write String(grUser.getValue('mobile')).length > 15 also

                  gs.log('User: ' + grUser.name + 'with sys_id: ' + grUser.sys_id + ' have mobile number more than 15 chars');

}

Data Policy - ServiceNow Wiki

Mark if it is helpful or correct, feedback is appreciated

[James Fricker]

var gr = new GlideRecord('sys_user');
gr.addQuery('glidefunction:length(phone)>15');
gr.query();

done! its that easy.