SQL - split a string using "/" that occurs multiple times

Question

I'm looking for a query for HSQLDB.

I have a string that contains address information delimited by "/". Now I have to split this string by "/" and insert individual address information into separate columns.

Address =  /1234/CLAREVIEW////WILMINGTON/DE/19702

This needs to be split as

StreetNo = Address[1] = 1234
StreetName = Address[2] = CLAREVIEW
StreetType = Address[3] = 
City = Address[6] = WILMINGTON
StateCd = Address[7] = DE
ZipCd = Address[8] = 19702

How can i achieve this?

Answer 1

CREATE PROCEDURE with the REGEXP_SUBSTRING_ARRAY function to split into an array.

 REGEXP_SUBSTRING_ARRAY('/1234/CLAREVIEW////WILMINGTON/DE/19702', '/\p{Alnum}*');

Returns

 ARRAY['/1234','/CLAREVIEW','/','/','/','/WILMINGTON','/DE','/19702'] 

So the procedure should contain:

 CREATE PROCEDURE INSERT_USING_REGEXP (p1 VARCHAR(500)) 
  BEGIN ATOMIC
   DECLARE arr VARCHAR(200) ARRAY;
   SET arr = REGEXP_SUBSTRING_ARRAY(p1,'/\p{Alnum}*');
   INSERT INTO thetable ((StreetNo, StreetName, StreetType...) VALUES ( arr[1], arr[2], arr[3], ...);
  END;

Then

 CALL INSERT_USING_REGEXP('/1234/CLAREVIEW////WILMINGTON/DE/19702');