[Stackoverflow] [Progress OpenEdge ABL] PROGRESS DB extract string between semi colons - INSTR Function error

[Michael]

I'm querying a Progress DB via an OpenQuery in MS SQL Server.

I have a field (addr) that contains a string value in the below format:

text123; text 456; text 789; text 1011


I need to extract each value before the semi colon ; for separate columns

In T SQL I would do this using the below to extract the first part:

SELECT
SUBSTRING(addr,1,CHARINDEX(';', addr,1) - 1)
FROM MyTable


However, in Progress there is no CHARINDEX, but INSTR

This is my Openquery:

SELECT * FROM OPENQUERY (MyServer, 'SELECT addr, SUBSTRING("addr",1,INSTR('';'', "addr",1) - 1) as test FROM MyTable')


But I receive the following error:

OLE DB provider "MSDASQL" for linked server "MyServer" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Error in row.".


What am I missing?

Continue reading...