Text Datatype

A string of text is an arbitrary sequence of Unicode characters. How the characters are encoded for response will be dependent on the negotiated HTTP charset. If there are characters in the string that cannot be represented in the negotiated charset, they will be replaced. It is strongly recommended that all clients use UTF–8 to prevent this from happening.

Important! Matching behavior differs between endpoint versions:

On 2.0 endpoints, matching is case-insensitive. eg: 'FOO' == 'foo'
On 2.1 endpoints, matching is case-sensitive, to be more consistent with SQL. eg: 'FOO' != 'foo'. To make matches case-insensitive, you can use the upper(...) SoQL function, like $where=UPPER(field_name) = 'FOO'.

When using SoQL, string literals are created using the single quote ('). For example:

text_value='string literal'

To escape a single quote within a string, double it. For example:

text_value='Bob''s string'

The following table shows what operations can be used on strings:

Operation	Description
`<`	`TRUE` for strings that are alphanumerically before this string
`<=`	`TRUE` for strings that are alphanumerically before or equal to this string
`>`	`TRUE` for strings that are alphanumerically after this string
`>=`	`TRUE` for strings that are alphanumerically after or equal to this string
`=`	`TRUE` for strings that are equal to this string
`!=`	`TRUE` for strings that are not equal to this string
`IS NULL`	`TRUE` for strings that are `NULL`.
`IS NOT NULL`	`TRUE` for strings that are not `NULL`.
`\|\|`	Concatenate two strings together

The following table describes the functions that can be used with text strings.

Keyword Name	Description	Availability
`distinct`	Returns distinct set of records	2.1

Function Name	Description	Availability
`between ... and ...`	Returns TRUE for values in a given range	2.1
`case(...)`	Returns different values based on the evaluation of boolean comparisons	2.1
`count(...)`	Returns a count of a given set of records	2.0 and 2.1
`in(...)`	Matches values in a given set of options	2.1
`like '...'`	Allows for substring searches in text strings	2.1
`lower(...)`	Returns the lowercase equivalent of a string of text	2.1
`max(...)`	Returns the maximum of a given set of numbers	2.1
`min(...)`	Returns the minimum of a given set of numbers	2.1
`not in(...)`	Matches values not in a given set of options	2.1
`not like '...'`	Allows for matching text fields that do not contain a substring	2.1
`starts_with(...)`	Matches on text strings that start with a given substring	2.1
`upper(...)`	Returns the uppercase equivalent of a string of text	2.1

For example, to query the City of Chicago Salaries to get only those employees who work for the aviation department (AVIATION):

https://data.cityofchicago.org/resource/tt4n-kn4t.json?department=AVIATION

You could also use the starts_with(...) function to find all employees with CHIEF in their title:

https://data.cityofchicago.org/resource/tt4n-kn4t.json?$where=starts_with(job_titles, 'CHIEF')