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?

CREATE PROCEDURE with the REGEXP_SUBSTRING_ARRAY function to split into an array. Then INSERT INTO t (StreetNo, StreetName, StreetType...) VALUES ( arr[1], arr[2], arr[3], ...) — fredt, Sep 05 '17 at 21:47
@fredt I have never used REGEXP_SUBSTRING_ARRAY. I searched but couldnt find anything helpful. Can you provide me some examples please — keanu, Sep 06 '17 at 01:00

score 3 · Accepted Answer · answered Sep 06 '17 at 07:30

3

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');

answered Sep 06 '17 at 07:30

fredt

24,044
3
40
61

Thank you. This is perfect. – keanu Sep 08 '17 at 21:21
Alnum dint seem to work for me. I used [a-zA-Z0-9] instead – keanu Sep 08 '17 at 21:21
Is there a way i can expand the regex to include special characters, not just numbers and alphabets – keanu Sep 08 '17 at 21:50

score -1 · Answer 2 · answered Sep 05 '17 at 20:53

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

I took this answer from here but it should do the trick

Why do you think a SQL Server solution (using T-SQL) would work on HSQLDB? — , Sep 06 '17 at 07:32

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

2 Answers2