Searching in a array field

[Dr_Dre_247]

I am running into a situation with an array field in a progress database. The array is max 15 elements of 76 chars. In 1 of the elements the word 'LABEL:' and a color name eg YELLOW is stored. The exact element is unknown and position of the color name is also unknow. A search is required. What I was thinking of doing is to convert the entire array to char(1140), then use instr() function to find 'LABEL' and then find the first ';' after the start position of 'LABEL'. The use the substr() function to get the piece of text from the text string and present that. The problem I am having is the search for the ';'. This is a reserved char and an error 7587 comes up every time a ; is used. How can I get around this?? I get to the database via the datadirect ODBC driver. We are using version 9.1e SP4 and are planning for a migration to 10.2 soon. This solution should therefore work in both environments if possible. Thanks

I have been testing with the convert function and the locate function but convert gives the same result as cast() or plain column. LOCATE does not look for semi-colon similar to instr()

 

[apsrbstar]

I'm no expert, but perhaps the extract from the SQL-92 reference could help:

Array fields are created using the Progress 4GL Dictionary. Progress SQL-92 provides a​

mechanism for referencing and updating these arrays. Subscripted references are not supported.​

Progress SQL-92 manages the arrays as NVARCHAR strings, and the string representation is​

the concatenation of each array element, with a semicolon ( ; ) separating each element.​

To escape an instance of a semicolon in the data of a Progress CHARACTER array, use the​

escape character tilde before the semicolon ( ~; ). An unquoted question mark represents a​

NULL element. To distinguish a NULL element from a question mark, use a tilde as an escape​

character for a question mark in the data ( ~? ). A tilde is also the escape character for a​

tilde ( ~~ ).
​

Looks like you should just stick a ~ in front