Use addQuery with STARTSWITH and…

Server-sideintermediateTested on Xanadu

Chrome Extension

Sourdough: ServiceNow Monitoring and Analytics

A Chrome extension for ServiceNow Admins and Developers with essential tools, analytics, graphs and monitoring features.

Instance HealthGraphs & ChartsAPI HealthDeveloper ToolsQuick SearchInstance Switcher

Add to Chrome

Free to install. Pro $5/month after a 14-day no-card trial.
Pro requires the ServiceNow admin role. Upgrade inside the extension.

Overview

Tasks

CMDB

API

Metrics

Monitor

Internals

Instance:sourdoughdev·Version:Yokohama

Instance StateONLINE

System StatusFully Operational

Session Timeout90 minutes

Logged-In Sessions2 (20 active)

Build Nameyokohama-12-18-2024_p1

IP Address10.159.128.43

Instance HealthHealth Score: 90%

🔥 5dSourdough (Chrome Plugin)Dark Mode

Filter text fields with partial match operators like STARTSWITH and CONTAINS

Script Include

var UserUtil = Class.create();
UserUtil.prototype = {

    getUsersByNamePattern: function(namePrefix, descriptionKeyword) {
        if (!namePrefix && !descriptionKeyword) {
            return [];
        }

        var users = new GlideRecord('sys_user');
        users.addQuery('active', true);

        if (namePrefix) {
            users.addQuery('name', 'STARTSWITH', namePrefix);
        }

        if (descriptionKeyword) {
            // CONTAINS bypasses all database indexes
            users.addQuery('introduction', 'CONTAINS', descriptionKeyword);
        }

        users.setLimit(100); // Prevent runaway queries
        users.orderBy('name');
        users.query();

        var results = [];
        while (users.next()) {
            if (users.name.nil() || users.introduction.nil()) {
                continue; // Skip incomplete records
            }

            results.push({
                sys_id: users.getUniqueValue(),
                name: users.getValue('name'),
                email: users.getValue('email'),
                introduction: users.getValue('introduction')
            });
        }

        return results;
    },

    type: 'UserUtil'
};

ServiceNow's STARTSWITH and CONTAINS operators look identical in the API but behave radically differently at the database level. STARTSWITH can leverage database indexes for prefix matching, while CONTAINS forces a full table scan regardless of indexes. Most developers discover this performance cliff only after their scheduled job starts timing out in production on a 500k-record table.

When to use this

When building user search functionality that needs to match name prefixes ("Find users starting with 'John'")
When the CONTAINS result set is bounded by other indexed filters and you control the query scope
When processing incident numbers or request numbers that follow consistent prefixes ("INC", "REQ", "CHG")
When building scheduled jobs that need partial text matching with proper setLimit() protection

When NOT to use this

Don't use CONTAINS on large tables without other restrictive filters — use Text Search instead
Don't use either operator in client scripts — build server-side endpoints with proper caching
Don't use CONTAINS in Business Rules that fire on every record insert — you'll create database contention
Don't use these operators when you need exact matches — use standard addQuery('field', 'value') for better performance

Key behaviors and gotchas

CONTAINS queries ignore all database indexes and perform full table scans — execution time scales linearly with table size
STARTSWITH can use database indexes but only when the field has an index and the prefix is at least 3 characters
Both operators are case-insensitive by default — STARTSWITH 'john' matches "John Smith" and "JOHNSON"
Domain separation affects results — queries only return records visible to the current domain context
ACLs are enforced — records that fail read ACL checks are silently excluded from results
Always use setLimit() with CONTAINS queries — without it, a single query can consume server resources for minutes

⚠️

CONTAINS queries on tables with more than 100k records will cause performance issues in production. Always combine with restrictive indexed filters (like active=true, created within date range) or use ServiceNow's Text Search functionality instead.

Free Newsletter

Enjoying this? Get one deep-dive per week.

Join 1,000+ ServiceNow pros — scripts, GlideRecord patterns, Flow Designer techniques, and career moves. Free.

Alternative Approaches

Text Search API for full-text queries

var textSearch = new GlideTextSearch('sys_user');
textSearch.setTable('sys_user');
textSearch.addSearchText('introduction', searchKeyword);
textSearch.setLimit(50);
var results = textSearch.search();

while (results.next()) {
    gs.info('Found user: ' + results.getValue('name'));
}

Use when you need to search across multiple text fields or when CONTAINS performance is inadequate.

Trade-offs: More complex setup but significantly better performance on large datasets with proper indexing.

API Methods Used

GlideRecord.addQuery()GlideRecord.setLimit()GlideRecord.orderBy()GlideRecord.query()

Test Your Knowledge

Quick 3-question quiz — see how your ServiceNow skills stack up.

Question 1 of 3Performance

A list view on a table with millions of records is slow. Best fix?

Select an answer to continue

Use addQuery with STARTSWITH and CONTAINS operators