INSTR with an Occurrence that needs to be a variable - help please! - Forum - OpenEdge Development - Progress Community

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

 Forum

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

This question is answered

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 ')


Verified Answer
  • 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
  • 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.

  • 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.

  • 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
  • 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!!!