INSTR with an Occurrence that needs to be a variable - help

Posted by seaside_escape on 10-Oct-2018 12:57

I need to pick out some string from a string that varies in length. The end result is to pick out the id number, contained in brackets, from a string.

An example of the data is:

Jones (4)
Smith (Deceased) (100)

The result from the above example would need to be:

4
100

Ideally I want a query that will find the start position of the last open bracket and pick up the data from that point onwards, because the id number, contained in brackets, is always at the end.

Unfortunately, REVERSECHARINDEX and PATINDEX are not available in OpenEdge SQL functions so I cannot use those.

INSTR(contact_data,'(',1,2) gives me the start point, but requires me to provide the nth occurrence - which is a variable because some data contains 1 bracket, some contain more.

I have tried to introduce a variable into occurrences, but it errors - please see below and please help if you can!

SELECT * FROM OPENQUERY(PROCLAIM,'
SELECT a.contact_data,
INSTR(a.contact_data,''('',1,a.bracket_occurrences) AS searching_for_start_point,
SUBSTRING(a.contact_data,LOCATE(''('',a.contact_data,1)+1,LENGTH(a.contact_data)-LOCATE(''('',a.contact_data,1)-1) AS contact_id FROM( SELECT contact_data, CAST(LENGTH(contact_data) - LENGTH(REPLACE(contact_data,''('','''')) AS INT) AS bracket_occurrences FROM PUB.contacts ) AS a ')


Posted by Stefan Drissen on 10-Oct-2018 17:03

My best stackoverflow answer:

select 
  contact_data, 
  rtrim(
    substring (
      contact_data,
      greatest( 
        instr( contact_data, '(', 1, 1 ), 
        instr( contact_data, '(', 1, 2 ), 
        instr( contact_data, '(', 1, 3 ) 
      ) + 1
    ), 
    ')'
  )
from pub.contacts

All Replies

Posted by Patrick Tingen on 10-Oct-2018 16:21

Do you need to have this in SQL? In the good old ABL this would be almost trivial

DEFINE VARIABLE cString AS CHARACTER   NO-UNDO.

cString = 'hello world (12) (23)'.

MESSAGE

 ENTRY(1, ENTRY(NUM-ENTRIES(cString,'('), cString,'('), ')')

 VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.

Posted by Rick Terrell on 10-Oct-2018 16:42

Check out “r-index”. 

Rick Terrell 
Principle Consultant, Professional Services 
Progress

Sent from my iPhone

On Oct 10, 2018, at 5:21 PM, seaside_escape <bounce-seaside_escape@community.progress.com> wrote:

Update from Progress Community
seaside_escape

I need to pick out some string from a string that varies in length. The end result is to pick out the id number, contained in brackets, from a string.

An example of the data is:

Jones (4)
Smith (Deceased) (100)

The result from the above example would need to be:

4
100

Ideally I want a query that will find the start position of the last open bracket and pick up the data from that point onwards, because the id number, contained in brackets, is always at the end.

Unfortunately, REVERSECHARINDEX and PATINDEX are not available in OpenEdge SQL functions so I cannot use those.

INSTR(contact_data,'(',1,2) gives me the start point, but requires me to provide the nth occurrence - which is a variable because some data contains 1 bracket, some contain more.

I have tried to introduce a variable into occurrences, but it errors - please see below and please help if you can!

SELECT * FROM OPENQUERY(PROCLAIM,'
SELECT a.contact_data,
INSTR(a.contact_data,''('',1,a.bracket_occurrences) AS searching_for_start_point,
SUBSTRING(a.contact_data,LOCATE(''('',a.contact_data,1)+1,LENGTH(a.contact_data)-LOCATE(''('',a.contact_data,1)-1) AS contact_id FROM( SELECT contact_data, CAST(LENGTH(contact_data) - LENGTH(REPLACE(contact_data,''('','''')) AS INT) AS bracket_occurrences FROM PUB.contacts ) AS a ')


View online

 

You received this notification because you subscribed to the forum.  To stop receiving updates from only this thread, go here.

Flag this post as spam/abuse.

Posted by Stefan Drissen on 10-Oct-2018 17:03

My best stackoverflow answer:

select 
  contact_data, 
  rtrim(
    substring (
      contact_data,
      greatest( 
        instr( contact_data, '(', 1, 1 ), 
        instr( contact_data, '(', 1, 2 ), 
        instr( contact_data, '(', 1, 3 ) 
      ) + 1
    ), 
    ')'
  )
from pub.contacts

Posted by seaside_escape on 11-Oct-2018 03:54

Hi Stefan.  Thank you for your answer here.  It works really well. I'll probably add in a few more INSTR's to make sure I cover lots of possibilities. Amazing!!!

This thread is closed