Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: SQL : How to get Last word if a Character String ?

    Hi,

    How to find Last word in a sentence.

    I need to extract 'ddd' out of 'aaa bbb ccc ddd' when there may variable number of spaces in the string.

    Help appreciated.

    Thanks

    DBFinder

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT in_string
         , SUBSTR( in_string , LOCATE_IN_STRING( in_string , ' ' , 1 , ( LENGTH(in_string) - LENGTH( REPLACE(in_string , ' ' , '') ) ) ) + 1 ) last_word
      FROM (VALUES 'aaa bbb ccc ddd') t(in_string);
    ------------------------------------------------------------------------------
    
    IN_STRING       LAST_WORD      
    --------------- ---------------
    aaa bbb ccc ddd ddd            
    
      1 record(s) selected.
    Last edited by tonkuma; 01-11-10 at 17:26.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If LOCATE_IN_STRING was not supported on your DB2,
    please try following example.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT in_string
         , SUBSTR( in_string
                 , (SELECT MAX(CASE SUBSTR(in_string, pos, 1) WHEN ' ' THEN pos END) + 1
                      FROM (SELECT 1+n1+n2*10+n3*100+n4*1000+n5*10000
                              FROM (VALUES 0,1,2,3,4,5,6,7,8,9)n1(n1)
                                 , (VALUES 0,1,2,3,4,5,6,7,8,9)n2(n2)
                                 , (VALUES 0,1,2,3,4,5,6,7,8,9)n3(n3)
                                 , (VALUES 0,1,2,3,4,5,6,7,8,9)n4(n4)
                                 , (VALUES 0,1,2,3            )n5(n5)
                           ) s(pos)
                     WHERE pos <= LENGTH(in_string)
                   )
                 ) AS last_word
      FROM (VALUES 'aaa bbb ccc ddd') t(in_string);
    ------------------------------------------------------------------------------
    
    IN_STRING       LAST_WORD      
    --------------- ---------------
    aaa bbb ccc ddd ddd            
    
      1 record(s) selected.

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks

    LOCATE_IN_STRING does not work for me (LUW).

    I may write user-defined function, but for now, its a great help. Thanks

    DBFinder

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

    Wink In more interesting way

    You can do it without any special function, using the original logics:

    Code:
    with in_str (in_string) as
    (
    select 'abc   def ghj   klmnpr'
    from sysibm.sysdummy1
    )
    , 
    str_info(in_string, ln_string) as
    (select rtrim(in_string), length(rtrim(in_string))
    from in_str 
    )  
    ,
    rev_last_word (in_string, ln_string, k, rev_last_word, L) as
    (select in_string, ln_string, ln_string + 1, varchar('', 1000), 0
     from str_info
    union all
    select in_string, ln_string, k - 1, 
             rev_last_word || substr(in_string, k - 1, 1), L + 1
    from rev_last_word 
    where K > 2 and substr(in_string, k - 1, 1) > ' '
    ) 
    , 
    last_word (last_word, K, rev_last_word) as
    (select varchar('', 1000), L + 1, rev_last_word
     from rev_last_word 
     where L = (select max(L) from rev_last_word ) 
    union all
    select last_word || substr(rev_last_word, k - 1, 1),  
           K - 1, rev_last_word
    from  last_word 
    where K >= 2  
    ) 
    select last_word  
    from last_word where k = 1
    Original string: 'abc def ghj klmnpr'
    Result _string: 'klmnpr'

    Lenny
    Last edited by Lenny77; 01-11-10 at 19:06.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to create a UDF,
    here is an example.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION last_word(in_string VARCHAR(4000) )
     RETURNS VARCHAR(4000)
     READS SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
    RETURN
    WITH find_last_blank(pos) AS (
    VALUES LENGTH(in_string)
    UNION ALL
    SELECT pos - 1
      FROM find_last_blank
     WHERE pos > 0
       AND SUBSTR(in_string, pos, 1) <> ' '
    )
    SELECT SUBSTR(in_string, MIN(pos) + 1)
      FROM find_last_blank
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Usage example
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT in_string
         , CAST(last_word(in_string) AS VARCHAR(50) ) AS last_word
      FROM (VALUES 'aaa bbb ccc ddd') t(in_string);
    ------------------------------------------------------------------------------
    
    IN_STRING       LAST_WORD                                         
    --------------- --------------------------------------------------
    aaa bbb ccc ddd ddd                                               
    
      1 record(s) selected.

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Great, works.

    I had created udf last night as
    Code:
    CREATE FUNCTION RIGHTSTR(SEPSTR VARCHAR(4000),INSTR VARCHAR(4000))
         RETURNS VARCHAR(4000)
         DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
         BEGIN ATOMIC
         DECLARE RSTR VARCHAR(4000) DEFAULT '';
         DECLARE POS INT;
         IF INSTR IS NULL THEN
         RETURN NULL;
         END IF;
         SET RSTR= RTRIM(INSTR);
         SET POS = LOCATE(SEPSTR,RSTR);
         WHILE POS > 0 DO 
         SET RSTR = SUBSTR(RSTR,POS+LENGTH(SEPSTR));
         SET POS = LOCATE(SEPSTR,RSTR);
         END WHILE;
         RETURN RSTR;
       END

    Thanks,

    DBFinder

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It would be not neccesary to use RSTR.

    And, if INSTR is null, then RSTR will be null by "SET RSTR= RTRIM(INSTR);".

    Here is an example.

    Create two parameter function:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION last_word( in_string VARCHAR(4000) , sep_string VARCHAR(1) )
     RETURNS VARCHAR(4000)
     SPECIFIC last_word_2parm
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
    BEGIN ATOMIC
    DECLARE pos INTEGER;
    
    SET pos = LENGTH(in_string);
    WHILE pos > 0 AND SUBSTR(in_string, pos, 1) <> sep_string DO
       SET pos = pos - 1;
    END WHILE;
    
    RETURN
     SUBSTR(in_string , pos + 1);
    
    END@
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Create one parameter function:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION last_word( in_string VARCHAR(4000) )
     RETURNS VARCHAR(4000)
     SPECIFIC last_word_1parm
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
    RETURN last_word(in_string , ' ');
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Sample usage:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT char_string
         , CAST(last_word(char_string, ' ') AS VARCHAR(15) ) AS last_word_2parm
         , CAST(last_word(char_string)      AS VARCHAR(15) ) AS last_word_1parm
         , CAST(last_word(char_string, 'b') AS VARCHAR(15) ) AS last_word_sep_b
    
      FROM (VALUES 'aaa bbb ccc ddd' , CAST(NULL AS VARCHAR(1) ) ) t(char_string)
    ;
    ------------------------------------------------------------------------------
    
    CHAR_STRING     LAST_WORD_2PARM LAST_WORD_1PARM LAST_WORD_SEP_B
    --------------- --------------- --------------- ---------------
    aaa bbb ccc ddd ddd             ddd              ccc ddd       
    -               -               -               -              
    
      2 record(s) selected.
    Last edited by tonkuma; 01-12-10 at 03:50. Reason: Added "pos > 0 AND" for the case "no sep_string in in_string".

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to search from the beginning of INSTR,
    this may be a little simpler(and I would expect a little better performance).

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION rightstr( sep_string VARCHAR(1) , in_string VARCHAR(4000) )
     RETURNS VARCHAR(4000)
     SPECIFIC rightstr_2parm
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
    BEGIN ATOMIC
    DECLARE pos, new_pos INTEGER DEFAULT 1;
    
    SET new_pos = LOCATE(sep_string, in_string);
    
    WHILE new_pos > 0 DO
       SET pos = new_pos + 1;
       SET new_pos = LOCATE(sep_string, in_string, pos);
    END WHILE;
    
    RETURN SUBSTR(in_string, pos);
    
    END@
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION rightstr( in_string VARCHAR(4000) )
     RETURNS VARCHAR(4000)
     SPECIFIC rightstr_1parm
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
    RETURN rightstr(' ' , in_string)
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT char_string
         , CAST(rightstr(' ', char_string) AS VARCHAR(15) ) AS rightstr_2parm
         , CAST(rightstr(char_string)      AS VARCHAR(15) ) AS rightstr_1parm
         , CAST(rightstr('b', char_string) AS VARCHAR(15) ) AS rightstr_sep_b
         , CAST(rightstr('x', char_string) AS VARCHAR(15) ) AS rightstr_sep_x
    
      FROM (VALUES 'aaa bbb ccc ddd' , CAST(NULL AS VARCHAR(1) ) ) t(char_string)
    ;
    ------------------------------------------------------------------------------
    
    CHAR_STRING     RIGHTSTR_2PARM  RIGHTSTR_1PARM  RIGHTSTR_SEP_B  RIGHTSTR_SEP_X 
    --------------- --------------- --------------- --------------- ---------------
    aaa bbb ccc ddd ddd             ddd              ccc ddd        aaa bbb ccc ddd
    -               -               -               -               -              
    
      2 record(s) selected.
    Last edited by tonkuma; 01-12-10 at 03:41. Reason: Added test case "rightstr_sep_x".

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Right,

    Appreciated.

    DBFinder

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

    Lightbulb Simplification

    As usual, we can simplify query by removing one extra operation:

    Code:
    with in_str (in_string) as
    (
    select 'abc   def ghj   klmnpr'
    from sysibm.sysdummy1
    )
    , 
    str_info(in_string, ln_string) as
    (select rtrim(in_string), length(rtrim(in_string))
    from in_str 
    )  
    ,
    last_word (in_string, ln_string, k, last_word, L) as
    (select in_string, ln_string, ln_string + 1, varchar('', 1000), 0
     from str_info
    union all
    select in_string, ln_string, k - 1, 
            substr(in_string, k - 1, 1) || last_word, L + 1
    from last_word 
    where K >= 2 and substr(in_string, k - 1, 1) > ' '
    ) 
    select last_word 
    from last_word
    where L = (select max(L) from last_word )
    Lenny
    Last edited by Lenny77; 01-12-10 at 12:17.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DBFinder View Post
    How to find Last word in a sentence.
    create a REVERSE function (since DB2 apparently doesn't have one)

    there's one here -- Sample UDFs for Migration

    then reverse the string, find the first space, take the substring, and reverse the result (this step is trivial)


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    Thumbs down

    Quote Originally Posted by r937 View Post
    create a REVERSE function (since DB2 apparently doesn't have one)

    there's one here -- Sample UDFs for Migration

    then reverse the string, find the first space, take the substring, and reverse the result (this step is trivial)


    I believe you can read. Just take a look on two queries above...

    Lenny

  14. #14
    Join Date
    Jan 2010
    Posts
    4
    Hi

    How to find the 500th record in 1000 record Db2 table?

    Buvan

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

    Thumbs down

    Quote Originally Posted by mbuvan View Post
    Hi

    How to find the 500th record in 1000 record Db2 table?

    Buvan
    The number of row is virtual in DB2 and depends on ORDER BY....

    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
  •