SQL API — JDBC-Only Supported Functions and Clauses - Support and Troubleshooting

SQL API — JDBC-Only Supported Functions and Clauses .ns-kb-css-body-editor-container { p { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } span { font-size: 12pt; font-family: Lato; color: var(--now-color--text-primary, #000000); } h2 { font-size: 24pt; font-family: Lato; color: var(--now-color--text-primary, black); } h3 { font-size: 18pt; font-family: Lato; color: var(--now-color--text-primary, black); } h4 { font-size: 14pt; font-family: Lato; color: var(--now-color--text-primary, black); } a { font-size: 12pt; font-family: Lato; color: var(--now-color--link-primary, #00718F); } a:hover { font-size: 12pt; color: var(--now-color--link-primary, #024F69); } a:target { font-size: 12pt; color: var(--now-color--link-primary, #032D42); } a:visited { font-size: 12pt; color: var(--now-color--link-primary, #00718f); } ul { font-size: 12pt; font-family: Lato; } li { font-size: 12pt; font-family: Lato; } img { display: ; max-width: ; width: ; height: ; } } body { font-family: Arial, sans-serif; font-size: 14px; color: #1d1d1d; max-width: 960px; margin: 40px auto; padding: 0 24px; line-height: 1.6; } p { margin: 10px 0; } ul, ol { margin: 8px 0 8px 24px; } li { margin-bottom: 4px; } table { border-collapse: collapse; width: 100%; margin: 16px 0; font-size: 13px; } th { background-color: #f0f0f0; text-align: left; padding: 8px 10px; border: 1px solid #ccc; } td { padding: 7px 10px; border: 1px solid #ccc; vertical-align: top; } tr:nth-child(even) { background-color: #fafafa; } .warning { background-color: #fff8e1; border-left: 4px solid #f5a623; padding: 10px 14px; margin: 16px 0; border-radius: 2px; } .warning strong { color: #b36200; } .note { background-color: #f5f5f5; border-left: 4px solid #999; padding: 10px 14px; margin: 10px 0; font-size: 13px; border-radius: 2px; } hr { border: none; border-top: 1px solid #ddd; margin: 28px 0; } This article lists the SQL functions and clauses that are supported when using the ServiceNow SQL API with the JDBC driver but are not supported with the ODBC driver. If you are using the ODBC driver and a query that uses any of these functions or clauses fails, this is expected behavior — these operations are not available over ODBC.

Note: The functions and clauses listed below work only with the JDBC driver . They are not supported with the ODBC driver. If your use case requires any of these, you must use the JDBC driver to connect to the SQL API. Functions and Clauses Supported in JDBC Only

Category Function / Clause Description Operators <> Performs a logical exclusive OR operation. Clauses EXCEPT Returns rows from the first query that are not present in the second query. Clauses INTERSECT Returns only the rows that are common to both result sets. Clauses LIMIT Restricts the number of rows returned by a query. Use this to control result set size and avoid timeouts. Aggregate Fn AVG DISTINCT Returns the average of distinct (unique) values only, ignoring duplicates. Aggregate Fn BOOL AND Returns true only if all input boolean values are true; returns false if any value is false. Aggregate Fn BOOL OR Returns true if at least one input boolean value is true; returns false only if all values are false. Aggregate Fn STDDEV Returns the standard deviation of the input values. Aggregate Fn STDDEV_POP Returns the population standard deviation, calculated over all values in the group. Aggregate Fn STDDEV_SAMP Returns the sample standard deviation, calculated over a subset of values in the group. Aggregate Fn SUM DISTINCT Returns the sum of distinct (unique) values only, ignoring duplicates. Aggregate Fn VAR_POP Returns the population variance, calculated over all values in the group. Aggregate Fn VAR_SAMP Returns the sample variance, calculated over a subset of values in the group. String FN !~ (not match) Returns true if the string does not match the given regular expression (case-sensitive). String FN !~* (not match ci) Returns true if the string does not match the given regular expression (case-insensitive). String FN || (concat) Concatenates two or more strings into a single string using the pipe operator. String FN ~ (regex) Returns true if the string matches the given regular expression (case-sensitive). String FN ~* (regex ci) Returns true if the string matches the given regular expression (case-insensitive). String FN CAST Explicitly converts a value from one data type to another. For example, converting a string to an integer or a date. String FN CHR Returns the character corresponding to the given ASCII code. For example, CHR(65) returns 'A'. String FN CONCAT Concatenates two or more strings into a single string. String FN CONVERT_FROM Converts a binary string from a specified encoding (such as UTF-8) to the database's default encoding. String FN DECODE Decodes a binary string that was encoded using ENCODE, converting it back to its original form. String FN ENCODE Encodes binary data into a text representation using a specified format such as hex or base64. String FN ILIKE Performs case-insensitive pattern matching, similar to LIKE but ignoring letter case. String FN n::bit(32)::text Casts an integer to a 32-bit binary representation and returns it as a text string. String FN NOT REGEXP Returns true if the string does not match the given regular expression pattern. String FN OVERLAY Replaces a portion of a string with another string, starting at a specified position. String FN POSITION Returns the starting position of a substring within a string. Returns 0 if the substring is not found. String FN REGEX (~) Returns true if the string matches the given regular expression pattern. String FN REPEAT Returns the input string repeated a specified number of times. String FN ROUND Rounds a numeric value to the nearest integer or to a specified number of decimal places. String FN SUBSTRING Extracts a portion of a string starting from a specified position, with an optional length. String FN TO_DATE Converts a string to a date value using a specified format pattern. For example, TO_DATE('2024-01-15', 'YYYY-MM-DD'). String FN TO_NUMBER Converts a string to a numeric value using a specified format pattern. String FN TO_TIMESTAMP Converts a string to a timestamp value using a specified format pattern. DateTime FN CURRENT_DATE Returns the current date without the time component. DateTime FN CURRENT_TIME Returns the current time without the date component. DateTime FN CURRENT_TIMESTAMP Returns the current date and time including the time zone. DateTime FN DATE_ADD (+) Adds a specified time interval (such as days or months) to a date and returns the resulting date. DateTime FN DATE_SUBTRACT (-) Subtracts a specified time interval from a date and returns the resulting date. DateTime FN EXTRACT Extracts a specific part from a date or time value, such as year, month, day, or hour. DateTime FN JUSTIFY_DAYS Normalizes an interval by converting days exceeding a full month into months. DateTime FN JUSTIFY_HOURS Normalizes an interval by converting hours exceeding a full day into days. Numeric FN GET_BIT Returns the value (0 or 1) of a specific bit at a given position in a bit string. Numeric FN GET_BYTE Returns the value of a specific byte at a given position in a binary string. Numeric FN LOG(base, x) Returns the logarithm of x for the specified base. For example, LOG(10, 100) returns 2. Numeric FN RADIANS Converts a numeric value from degrees to radians. Windows FN CUME_DIST() Returns the cumulative distribution of a value within its partition — the fraction of rows with values less than or equal to the current row's value. Windows FN FIRST_VALUE() Returns the value of the specified expression for the first row in the current window frame. Windows FN LAG() Returns the value of the specified expression from a row a given number of positions before the current row. Windows FN LAST_VALUE() Returns the value of the specified expression for the last row in the current window frame. Windows FN LEAD() Returns the value of the specified expression from a row a given number of positions after the current row. Windows FN NTILE() Divides the rows in a partition into a specified number of equal-sized buckets and assigns a bucket number to each row. Windows FN PERCENT_RANK() Returns the relative rank of a row within its partition as a value between 0 and 1. Other SET_BIT Sets the bit at a specified position in a bit string to a given value (0 or 1) and returns the modified string. Other SET_BYTE Sets the byte at a specified position in a binary string to a given value and returns the modified string. XML FN XPATH_EXISTS Evaluates an XPath expression against an XML value and returns true if the expression matches any nodes, false otherwise.