PROGRESS DB extract string between semi colons - INSTR Function error

Question

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?

Why are you storing delimited data in the first place? Why not split the string in the outer query? There was literally a [question asking how to do](https://stackoverflow.com/q/60053079/2029983) this a few questions ago in [tag:sql-server] (and it's a very common question anyway). — Thom A, Feb 04 '20 at 09:16
Hi @Larnu its from a 3rd party system that I have no control over. All I have is read only access to it. — Michael, Feb 04 '20 at 09:22
What if you do that on the SQL Server side? instead of `Select * from OPENQUERY(...)` do `Select SUBSTRING(addr,1,CHARINDEX(';', addr,1) - 1) from OPENQUERY(...)` — Zohar Peled, Feb 04 '20 at 10:02
@ZoharPeled Yes I can, but wanted to do in the OpenQuery so the Progress server actually performs instead of SQL Server first bringing all the info across then doing it. If I can't work it out, I will do it on the SQL Server side. — Michael, Feb 04 '20 at 11:35

score 0 · Answer 1 · answered Feb 04 '20 at 20:26

Here's how I would handle this...

PostgreSQL:

WITH -- YUOUR TEXT HERE:
someString AS (SELECT 'text123; text 456; text 789; text 1011' AS txt), -- Can come in as a variable
a          AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(N)),
iTally(N)  AS 
(
  SELECT 0 UNION ALL
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM   a, a AS b, a AS c, a AS d
),
ngrams(N,Token) AS
(
  SELECT     N, SUBSTRING(txt from N::integer for 1)
  FROM       iTally
  CROSS JOIN someString
  WHERE      N <= CHAR_LENGTH(txt)
)
SELECT 
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ItemNumber,
  (N+1)                          AS ItemIndex,
  len                            AS ItemLength,
  SUBSTRING(ntxt from 1 for len) AS Item
FROM       ngrams
CROSS JOIN someString
CROSS JOIN LATERAL (VALUES(SUBSTRING(txt from (N+1)::integer for CHAR_LENGTH(txt))))  AS eg(ntxt)
CROSS JOIN LATERAL (VALUES(COALESCE(NULLIF(strpos(ntxt,';'),0),CHAR_LENGTH(ntxt))-1)) AS item(len)
WHERE N = 0 OR token LIKE ';%';

Results:

ItemNumber  ItemIndex  ItemLength  Item
----------- ---------- ----------- -----------
1           1          7           text123
2           9          9            text 456
3           19         9            text 789
4           29         9            text 101

A little trimming may be needed for the return item

Thanks - but this is Progress not PostgreSQL – Michael Feb 05 '20 at 08:50 — Michael, Feb 05 '20 at 08:50

score 0 · Answer 2 · answered Feb 08 '20 at 15:29

Extent / array fields in Progress OpenEdge are also shown as separated by a semi-colon when viewed from SQL92, if this is an extent / array field, then you can simply use:

addr[1]

If it is not, then you can use instr, but you will need to use the correct syntax. The first parameter is the string, the second is what you want to find in it. Note the extra + ';' below to ensure the string always ends with a semi-colon:

substr( addr, 1, instr( addr + ';', ';', 1 ) - 1 )

If you not only want the first, but the second or the third, then it gits a bit trickier:

select
  addr,  
  substring( addr, 1, instr( addr + ';', ';', 1, 1 ) ) as '#1' ,
  case    
     when instr( addr, ';', 1, 1 ) > 0 then
       substring( addr, instr( addr, ';', 1, 1 ), instr( addr + ';', ';', 1, 2 ) - instr( addr, ';', 1, 1 ) )
     else 
       ''
  end as '#2' ,
  case    
     when instr( addr, ';', 1, 2 ) > 0 then 
       substring( addr, instr( addr, ';', 1, 2 ), instr( addr + ';', ';', 1, 3 ) - instr( addr, ';', 1, 2 ) )
     else 
       ''
  end as '#3'  
from ...

All of the above has been tested from PDSOE (Eclipse) using the JDBC driver. Assuming OPENQUERY does not corrupt anything along the way, you should be fine.

I tried the above but receive: OLE DB provider "MSDASQL" for linked server "MyServer" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Inconsistent types (7481)". — Michael, Feb 10 '20 at 12:10
You are able to get just the contents of addr? What is the data type of addr? Is it an extent (if not, then you cannot use [1])? Additional, can you query directly (using odbc)? Sometimes it helps to not let OPENQUERY throw extra confusion into the mix. — Stefan Drissen, Feb 10 '20 at 12:56
I can get to addr yes, it is just varchar. To be honest, I will just use the outer T-SQL to do this, it will be easier. I just wanted to see if it was possible using Progress. Thank you anyway. — Michael, Feb 10 '20 at 13:07

PROGRESS DB extract string between semi colons - INSTR Function error

2 Answers2