Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    5

    Unanswered: Using locate with right function

    How can i locate the position of the last underscore in the string 'file_name_may'. Is will be using right and locate function. But could anyone let me know the syntax.
    I need to extract the characters after the last underscore. For eg. in this string may

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use REVERSE UDF or INSTR(a synonym for the LOCATE_IN_STRING).

    Samples are here:
    http://www.dbforums.com/db2/1654841-...problem-2.html

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink DB2 for beginners

    It is much easier, is not ?

    Code:
    with posn(psn) as
    (select 1 from sysibm.sysdummy1
    union all
    select psn + 1 
    from posn where psn <= 254
    )  
    select max(psn) last_location_of_searching_smb
    from posn
    join (select 'file_name_may' str from sysibm.sysdummy1) s
    On substr(str, psn, 1) = '_'
    Result:

    LAST_LOCATION_OF_SEARCHING_SMB
    10
    Lenny

  4. #4
    Join Date
    May 2010
    Posts
    5
    Thanks a ton tonkuma...it was helpful....

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Talking Different point of view

    Solution could be different:

    Code:
    with source (str) as 
    (select 'file_name_may  '
      from sysibm.sysdummy1
    ) 
    ,
    searching_str(srch) as
    (select '_' 
      from sysibm.sysdummy1
    )
    ,
    locate_all_ss (str, srch, ssposn) as
    (select  str, srch, locate(srch, str, 1)
       from  source, searching_str    
    union all
    select str, srch, locate(srch, str, ssposn + length(srch))
    from locate_all_ss 
    where locate(srch, str, ssposn + length(srch) ) > 0
      and length(srch) > 0
    ) 
    ,
    locate_last_ss_posn (str, srch, last_ss_posn) as
    (
    select str, srch, max(ssposn)
    from locate_all_ss 
    group by str, srch
    )
    select * from locate_last_ss_posn
    But result stays the same:

    STR.................. SRCH.........LAST_SS_POSN
    file_name_may...... _.................. 10
    Lenny

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool Get result for list of strings

    How you can see the last query working good with list of source and search strings:

    with source (str) as
    (select 'file_name_may '
    from sysibm.sysdummy1
    union all
    select 'search_string_not_found'
    from sysibm.sysdummy1
    )
    ,
    searching_str(srch) as
    (select '_'
    from sysibm.sysdummy1
    union all
    select '_n'
    from sysibm.sysdummy1
    )
    ,
    locate_all_ss (str, srch, ssposn) as
    (select str, srch, locate(srch, str, 1)
    from source, searching_str
    union all
    select str, srch, locate(srch, str, ssposn + length(srch))
    from locate_all_ss
    where locate(srch, str, ssposn + length(srch) ) > 0
    and length(srch) > 0
    )
    ,
    locate_last_ss_posn (str, srch, last_ss_posn) as
    (
    select str, srch, max(ssposn)
    from locate_all_ss
    group by str, srch
    )
    select * from locate_last_ss_posn
    Result:

    STR............................SRCH............ LAST_SS_POSN
    file_name_may............... ....._...............10
    file_name_may................... _n................5
    search_string_not_found........_...............18
    search_string_not_found......._n..............14
    Lenny

Posting Permissions

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