Find multiple substrings from a column entry in DB2

Question

I have a Table like this

|   ID   |  otherID  | Data
+--------+-----------+----------------
|  1234  |   56789   | This is a Name: Charlie; string <name_starts> with Name: Alpha; also some more data and Name: Bravo;

here the last column has a string and I need those names from it (there could be many Names), so the output should look like this

|   Name   
+------------------
|  Alpha;Bravo;

I wrote the following query that can retrieve the first name, but I need all the names and it is not known how many names are there

select substr(Data, locate('Name',Data)+6, locate(':',Data)-locate(':',Data)-6) from myTable

EDIT: Sorry I missed a point that I have to take all the names that occur after "name_starts", before that if there are any I need to ignore them. So sorry about missing this while posting.

Also, I'm using DB2-LUW v9.7

Answer 1

I don't have 10.5 at hand, but it works on 11.1:

WITH TAB (DATA) AS 
(
VALUES
  'This is a string with Name: Alpha; also some more data and Name: Bravo;'
)
SELECT 
--REGEXP_REPLACE(T.DATA, '.*? Name: ([^\s]+)', '$1') -- Since 11.1
xmlcast(xmlquery('fn:replace($s, ".*? Name: ([^\s]+)", "$1")' passing T.DATA as "s") as varchar(100))
FROM TAB T;