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:
'FOO' == 'foo'
'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')