KCL Software Solutions

Searching by Field Length

To retrieve records based on the length (number of characters) of a given field, you will use the LEN() function. This can be done on a single field or a combination of fields. For example:

LEN(DESCRIPTION) > 3000

To search on the length of a combined set of fields, concatenate them:

LEN(ISNULL(CMP_AltOrg,'') + ISNULL(CMP_FormerOrg,'') + ISNULL(LEGAL_ORG,'')) > 400

Note that it is important to use the ISNULL() function when concatenating fields together, or the results will be inaccurate. This is because when joining strings together, a null value eats up the other values, causing the whole string to be null.

Optionally, you can use a space or other string to join them, but this is not required:

LEN(ISNULL(CMP_AltOrg,'') + ' ' + ISNULL(CMP_FormerOrg,'') + ' ' + ISNULL(LEGAL_ORG,'')) > 400

If you do not know the name or structure of the field you wish to search, try doing a custom field search and then save your search to view the SQL.

Did you find this article helpful?