Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: Finding last Index of an Expression

    If an expression in a column is in the form of
    Measures.AllMeasures.NetSalesUnits.Cars how can I find the location of the last (.) in the expression. Is there any functions available.

    The number of (.) or the character to search is not pre-defined.

    posstr( ..) gives me the location of first occurrence

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    This function will do it...
    Code:
    CREATE FUNCTION LOCATELAST (ARG VARCHAR(10), STRING VARCHAR(2000))
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC
    
    RETURN
    WITH CHAR_POSITIONS(INDEX) AS
    (
        VALUES (0)
        UNION ALL
        SELECT COALESCE(LOCATE(ARG, STRING, INDEX + 1), 0)
        FROM CHAR_POSITIONS
        WHERE LOCATE(ARG, STRING, INDEX + 1) != 0
    )
    SELECT MAX(INDEX) FROM CHAR_POSITIONS
    ;
    e.g. db2 "values(locatelast('a', 'abcabc'))" would return '4'.


    Damian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •