TO_VECTOR (SQL)

Converts an array to a vector.

Synopsis

TO_VECTOR(data [, type ] [, length ])

Description

The TO_VECTOR function converts input data into a vector of the specified type. The input data will always be of type string, unless you are using the function in Dynamic SQL, in which case there are other accepted datatypes, such as a dynamic array.

This function can be used in INSERT statements to load vector-typed data into a table, or used in functions like VECTOR_DOT_PRODUCT to input a vector.

Arguments

data

A string that contains the data to be converted into a vector. Entries in the string must be separated by a comma (,). You can optionally use square brackets ([]) to indicate the beginning and end of the data.

If you are using TO_VECTOR in Dynamic SQL, other data types are accepted. The allowed data types are determined by the SelectMode. If the SelectMode is ODBC or Display, the data argument may be passed in as a DynamicArray, as well as a string. If the SelectMode is Logical, the data argument must be entered as a $vector.

type

An optional argument that specifies the intended datatype of the resulting vector. Possible values are the following literals: integer (accepts int), double, decimal, or string. If an element does not match the intended type, it is coerced into the type that is specified and no error is thrown. For conversions of double or decimal types to integers, the decimal places are truncated, not rounded.

If omitted, InterSystems SQL defaults to double. If elements in the vector have different data types (for example, one element is a string, while the others are doubles), the function fails and returns SQLCODE -1.

length

An optional argument that specifies the resulting length of the vector that is added to the table. If data has fewer elements than the value specified by length, the vector is padded with extra values. For numerically typed vectors, these extra values are 0. For string-typed vectors, these extra values are the empty string ('').

If data has more than length number of elements, the input data is truncated so the vector contains only the first length number of elements.

Examples

The following example executes an INSERT statement that uses TO_VECTOR to add a row to a table that stores words and their corresponding embeddings. Note that this example does not use a realistic number of dimensions to represent the embedding of the word “person.”

INSERT INTO Words.Embeddings (word,embedding) VALUES ("person", TO_VECTOR('1,3,5,7,9',int))