Determine the length of character fields

If you want to remap your array sub-element with a simple character data type, you must choose a length for the character field. Remember that multi-row array tables cannot use character large object data types. There are two methods to determine an appropriate length for your character data.

Search for	Example	Results
A single word	`cat`	Topics that contain the word "cat". You will also find its grammatical variations, such as "cats".
A phrase. You can specify that the search results contain a specific phrase.	`"cat food"` (quotation marks)	Topics that contain the literal phrase "cat food" and all its grammatical variations. Without the quotation marks, the query is equivalent to specifying an OR operator, which finds topics with one of the individual words instead of the phrase.

Search for	Operator	Example
Two or more words in the same topic	`AND` `and` `+` (plus symbol) `&` (ampersand)	`cat AND dog` `"cat food"+milk` `"cat food"&"dog food"`
Either word in a topic	`OR` `or` `\|` (pipe)	`cat OR dog` `cat \| dog`
Topics that do not contain a specific word or phrase	`NOT` `not` `!` (exclamation point)	`NOT cat` `! dog`
Topics that contain one string and do not contain another	`^` (caret)	`cat ^ mouse`
A combination of search types	`( )` parentheses	`cat + (dog \| mouse)` `cat \| dog + (! mouse)`

Match the length of the data source (preferred)

Review your existing data and add a padding

Ideally, you want to identify the source of your data and match its SQL mapping. For example, the source of the assignment.groups field is the name field of the assignment table. Since the name field is mapped as a varchar(60) data type, we will map our array to the same length of varchar(60).

You can also use your existing data to determine the proper size of your character fields. For example, if you review the source data of the assignment.groups field, you will see text strings from 20 to 40 character long.

You could therefore remap this data as a varchar(40) data type and likely not lose any data. However, you may want to provide the option to store assignment groups more than 40 characters long without having to store the array elements in separate rows. In this case, you may want to use a larger value such as varchar(50) or varchar(60).

When choosing a length value, you want to avoid selecting a SQL length shorter than the maximum length of your existing data. The system will wrap any existing data that exceeds the SQL length of the character field into a new row. Wrapping data into a new row can result in data corruption and broken queries. For example, if you select a length of varchar(10), then you any existing assignment group longer than ten characters will wrap into two or more rows. Using this mapping, the system would change an existing assignment group of "Application" to have one array element row with the value "Applicatio" and another array element row with the value "n". Any RDBMS query searching for the value "Application" would fail because there is no longer one element with this value.

Related concepts

Connect

Micro Focus Marketplace

ITSM Blog for IT Service Management

ITSM Community

Documentation Forum

Learn

Software Support Online

Software Support Downloads

Software Education Services

Contact

Send Help Center feedback

Submit Service Request

View Service Requests