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

Not open for further replies.


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:

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...
Not open for further replies.