Skip to main content


Returns the specified substring, based on a delimiter.




string An expression that resolves to a string. The target string from which a substring is to be returned. If you specify a null string ("") as the target string, FIELD always returns a null string.
delimiter An expression that resolves to a single character, specified as a number or a string. This character is used as a delimiter to identify substrings. This character cannot also be used as a data value within string. The delimiter characters used in dynamic arrays are listed in the Dynamic Arrays general concepts page of this manual.
count An expression that resolves to an integer that specifies which substring to return from the target string. Substrings are separated by a delimiter, and counted from 1. A decimal number is truncated to an integer. A string is parsed as a number until a non-numeric character is encountered. Thus “7dwarves” is parsed as 7. A count value of 0, a negative number, the null string, or a non-numeric string is the same as count=1.
range Optional — An expression that resolves to an integer specifying the number of delimited substrings to return, starting with count. If omitted, the default is 1.


The FIELD function returns the substring which is the nth piece of string, where the integer n is specified by the count parameter, and substrings are separated by a delimiter character. The delimiter itself is not returned.

If count is 1, FIELD returns the first piece of the string. This is the piece of the string from the beginning of the string to the first delimiter. If the first character of the string is a delimiter, count=1 returns the null string.

You can follow the FIELD function with the COL1 function to determine the string position of the start delimiter for the returned substring. If count is 1, COL1 returns 0. You can determine the end delimiter position by calling the COL2 function.

If count is greater than the number of delimited substrings, FIELD returns the null string. In this case, COL1 and COL2 both return 0.

If you specify a delimiter that is not located in string and count=1, FIELD returns the entire string. If count>1, FIELD returns the null string.

If you specify the null string as a delimiter, FIELD returns the entire string, regardless of the value of count.

If the optional range argument is set to an integer value greater than 1, that number of sequential delimited substrings is returned as a single string. Delimiters within the string are included. If range is a decimal number, it is truncated to its integer value. Setting range to any value other than a numeric 2 or greater is treated as setting it to 1. If range is larger than the number of remaining substrings in the string, the remaining substrings are returned.


The FIELD and GROUP functions are functionally identical.


By default Caché MVBasic permits only a single-character delimiter. jBASE emulation permits a multi-character delimiter. This option is set using the FULL.DELIM option.


The following example uses the FIELD function to return the first five delimited items in a string:

FOR x=1 TO 5
   PRINT FIELD(colors,"^",x)

The following example uses the FIELD function to return the first three elements in a dynamic array:

FOR x=1 TO 3
   PRINT FIELD(colors,CHAR(253),x)

The following example uses count and range:

PRINT FIELD(colors,"^",2,3)

Returns “Green^Blue^Yellow”.

See Also

FeedbackOpens in a new tab