1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

Query to collect last occurrence of a character in a string (SQL Progress OpenEdge)

Discussão em 'StackOverflow' iniciado por fdantas, Outubro 10, 2018.

  1. fdantas

    fdantas Administrator Moderador

    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, REVERSE, CHARINDEX 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
    ')


    Unfortunately, I only have access to a very limited set of SQL commands as I am using Progress OpenEdge.

    Below is a list of the SQL commands/functions available to me:

    ABS, ACOS, ADD_MONTHS, ASCII, ASIN, ATAN, ATAN2, AVG, CASE, CAST, CDC_get_changed_columns, CDC_is_column_changed, CEILING, CHAR, CHR, COALESCE, CONCAT, CONVERT (ODBC compatible), CONVERT (Progress extension), COS, COUNT, CURDATE, CURTIME, CURRVAL, DATABASE, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, DB_NAME, DECODE, DEGREES, EXP, FLOOR, GREATEST, HOUR, IFNULL, INITCAP, INSERT, INSTR, LAST_DAY, LCASE, LEAST, LEFT, LENGTH, LOCATE, LOG10, LOWER, LPAD, LTRIM, MAX, MIN, MINUTE, MOD, MONTH, MONTHNAME, MONTHS_BETWEEN, NEXT_DAY, NEXTVAL, NOW, NULLIF, NVL, PI, POWER, PREFIX, PRO_ARR_DESCAPE function, PRO_ARR_ESCAPE function, PRO_ELEMENT function, QUARTER, RADIANS, RAND, REPEAT, REPLACE, RIGHT, ROUND, ROWID, RPAD, RTRIM, SECOND, SIGN, SIN, SQRT, SUBSTR, SUBSTRING (ODBC compatible), SUFFIX, SUM, SYSDATE, SYSTIME, SYSTIMESTAMP, TAN, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIME, TO_TIMESTAMP, TRANSLATE, UCASE, UPPER, USER, WEEK, YEAR

    Continue reading...

Compartilhe esta Página