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?
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');
CREATE TABLE #Results
(
Ordinal NUMERIC,
StringValue VARCHAR(MAX)
)
DECLARE @String VARCHAR(100),
@Delimiter VARCHAR(100)
SET @String = '/1234/CLAREVIEW////WILMINGTON/DE/19702'
SET @Delimiter = '/'
DECLARE @TempString VARCHAR(MAX) = @String,
@Ordinal INT = 0,
@CharIndex INT = 0
SET @CharIndex = CHARINDEX(@Delimiter, @TempString)
WHILE @CharIndex != 0
BEGIN
SET @Ordinal += 1
INSERT #Results
VALUES (@Ordinal, SUBSTRING(@TempString, 0, @CharIndex))
SET @TempString = SUBSTRING(@TempString, @CharIndex + 1, LEN(@TempString) - @CharIndex)
SET @CharIndex = CHARINDEX(@Delimiter, @TempString)
END
IF @TempString != ''
BEGIN
SET @Ordinal += 1
INSERT #Results
VALUES (@Ordinal, @TempString)
END
SELECT *
FROM #Results
