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

DBFinder · #1 (**permalink**) 01-11-10, 16:00

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

tonkuma · #2 (**permalink**) 01-11-10, 16:21

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.

tonkuma · #3 (**permalink**) 01-11-10, 17:01

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.

DBFinder · #4 (**permalink**) 01-11-10, 17:33

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

Lenny77 · #5 (**permalink**) 01-11-10, 17:38

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

tonkuma · #6 (**permalink**) 01-11-10, 18:53

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.

DBFinder · #7 (**permalink**) 01-12-10, 01:17

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

tonkuma · #8 (**permalink**) 01-12-10, 02:08

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.

tonkuma · #9 (**permalink**) 01-12-10, 02:36

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.

DBFinder · #10 (**permalink**) 01-12-10, 07:12

Right,

Appreciated.

DBFinder

Lenny77 · #11 (**permalink**) 01-12-10, 11:05

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

r937 · #12 (**permalink**) 01-12-10, 12:54

Quote:

Originally Posted by DBFinder

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)

Lenny77 · #13 (**permalink**) 01-12-10, 13:56

Quote:

Originally Posted by r937

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