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.)

[Mike Allen]

This example is with email, but the same can be done with phone.

var user = new GlideRecord('sys_user');

user.query();

while(user.next()){

         var email = user.email.toString();

         var emailLength = email.length;

         if(emailLength > 25){

                   //do something about it

         }

}

Just put it in a background script. You could also just use gs.print to print it out,.

[johnsgp]

Thanks, Mike.   I thought of what you suggested.   I should have been more explicit that I don't want to do a walk through the entire table in Javascript.   In SQL I could write a WHERE clause such as "where length(email)>15)" but I can't see how to do this with the GlideRecord.   I seem to recall something that flashed past a while ago while I was browsing this forum but I can't find it now.

[Mike Allen]

Have you used gs.sql() before?   You can put a T-SQL statement as an argument in a background script and get the output.

[johnsgp]

Yes, I've used gs.sql to get what I want but I wanted a solution that is in a scheduled job or fix script instead.   So I'm specifically after whether this can be done with a GlideRecord (its an API question), rather than a solution to the problem.