Is this query possible ?

[AlexT]

Folks

Not sure how to tackle this in DB2...

I'd like to have a query that would check for the presence of a given
token in a text string and return the next "word".

Let's have Table1 with text field Field1:

Field1
---------------------------------------
This is a test !
Color blue or red !
Katrina is a CAT4 hurricane !

I'd like to search for "Color" and return the string "blue"
(the word delimiter being one or more space...

Any idea / suggestion welcome

Regards

--alexT

[Tonkuma]

------------------------- Commands Entered -------------------------
CREATE TABLE NextWord
(Field1 VARCHAR(30)
);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
INSERT INTO NextWord
VALUES
('This is a test')
,('Color blue or red')
,('Katrina is a CAT4 hurricane')
,('Color blue or red')
,('My Car Color is blue')
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------- Commands Entered -------------------------
SELECT Field1_Org
, SUBSTR(Field1,1,exist*(POSSTR(Field1,' ')-1)) Next_word
FROM (SELECT Field1
, LTRIM(SUBSTR(Field1,POSSTR(' '||Field1,' Color ')+6)) ||
' '
, SIGN(POSSTR(' ' || Field1,' Color '))
FROM NextWord
) Q(Field1_Org,Field1,exist);
------------------------------------------------------------------------------

FIELD1_ORG NEXT_WORD
------------------------------ -------------------------------
This is a test
Color blue or red blue
Katrina is a CAT4 hurricane
Color blue or red blue
My Car Color is blue is

5 record(s) selected.

[Tonkuma]

Minor revised example:
SELECT Field1
, SUBSTR(Field2,1,SIGN(POSSTR(' '||Field1,' Color '))
*(POSSTR(Field2,' ')-1)) Next_word
FROM (SELECT Field1
, LTRIM(SUBSTR(Field1,POSSTR(' '||Field1,' Color ')+6)) ||
' '
FROM NextWord
) Q(Field1,Field2);

[Brian Tkatch]

DECLARE GLOBAL TEMPORARY TABLE SESSION.NextWord(Field1 VARCHAR(30))

INSERT INTO SESSION.NextWord (Field1) \
VALUES \
('This is a test'), \
('Color blue or red'), \
('Katrina is a CAT4 hurricane'), \
('Color blue or red'), \
('My Car Color is blue'), \
('Blue is a very nice Color ')

SELECT \
SUBSTR \
( \
LTRIM(SUBSTR(Field1, LOCATE('Color ', Field1) + 6)), \
1, \
LOCATE \
( \
' ', \
LTRIM(SUBSTR(Field1, LOCATE('Color ', Field1) + 6)) \
) - 1 \
) \
FROM \
SESSION.NextWord \
WHERE \
LOCATE('Color ', Field1) > 0 \
AND LOCATE \
( \
' ', \
LTRIM(SUBSTR(Field1, LOCATE('Color ', Field1) + 6)) \
) - 1 > 0

DROP TABLE SESSION.NextWord

Of course, it'd be a lot easier if it wasn't 1 *or more* spaces.

B.

[AlexT]

Wow - thanks !

I've learn something :)