How split multiple strings in a column

[arfgh]

using sql and the character '0' as split, is there a way to split that string in a column without to care the number of splits and rows?

we can do as follow:

Code: Select all

	SELECT "S1" FROM 

	UNION SELECT "S1" FROM

	UNION SELECT "S1" FROM

etc

but the problem is to do it without care the number of possible splits.... i havent seen a way at the moment to do that, if possible ?

[Villeroy]

You really have a field of strings like "abc0cdef0g0hijk" with a varying count of elements?

[arfgh]

imagine the follow string, where the '*' is the character 0 hex. From that string and with sql, i want to split it in one column and all the rows that is possible because the character 0 to split, but without to take care of a still split number, 5, 10, 500.....etc

Code: Select all

white*blue*red*green*orange

At the moment and following the way i showed on the main op, i can split a still number of row in the colum, one plit for each table union. And that is the problem as you can see, when we dont care the number of splits... If in sql we can perform some kind of loop, it will be possible to perform it... but i dunno how to do it... if possible.

[arfgh]

the problem i see is to perform a loop with the detected number of characters 0 hex and repeat the table union for each split. Maybe is not possible with sql ?

[Villeroy]

Code: Select all

SELECT * FROM UNNEST( (SELECT REGEXP_SUBSTRING_ARRAY( "TXT", '.\x2C*' ) FROM TBL WHERE ID=13))

HSQL2 selects a column of split strings from a single value in TBL.TXT where row ID=13. The strings are split by commas (\x2C). I can't get it to work with \x00. I can't even store \x00 so I switched to comma.
Returns
a,
b,
c,
d,
e
from text 'a,b,c,d,e'

[arfgh]

no, that wasnt what i want...

[Villeroy]

It's just what I got after investing a little bit of time. A draft, an intermediate result, an approach, a vague idea. From this point the delimiter needs to be removed, the regex needs improvent ( .+?\x2C|.+$ might be more appropriate), we need some way to fetch the table's primary key into the result so we can query each split for each row ID. We are not here to serve you. At best we can help you.

[arfgh]

Code: Select all

white*blue*red*green*orange

with that string, and the '*' as split character. We want o obtain all splits string in a single comun and all the rows that are required to obtain the splits. But once that is done, the query should do the same with other split number.

[Sliderule]

no, that wasnt what i want...

Code: Select all

white*blue*red*green*orange

with that string, and the '*' as split character. We want o obtain all splits string in a single comun and all the rows that are required to obtain the splits. But once that is done, the query should do the same with other split number.

While I am not at all clear on what you want . . . the sample user Villeroy provided DOES work perfectly for me, and, with my understanding of what user arfgh wanted as output.

This example assumes the use of HSQL Version 2.x as the database back-end ( NOT the embedded HSQL database ( Version 1.8.0.10 ) ).

Code: Select all

-- Example of using HSQL Version 2.x back-end database with UNNEST for ARRAY processing
-- The table I am using is named "PEUGEOT_PART" and it contains various columns 
-- My column "DESCRIPTION" contains commas ( \x2C ) and a space ( \x2o ) in REGEXP_SUBSTRING_ARRAY function
SELECT 
   "PEUGEOT_PART"."ID",
   "PEUGEOT_PART"."DESCRIPTION",
   "PEUGEOT_PART"."PRICE",
   REPLACE("MY_TABLE"."MY_OUTPUT_STRING", ', ', SPACE(0)) as "MY_OUTPUT_STRING"
FROM "PEUGEOT_PART",
     -- Use HSQL 2.x UNNEST function for ARRAY processing
      UNNEST((
               SELECT 
                  REGEXP_SUBSTRING_ARRAY("PEUGEOT_PART_02"."DESCRIPTION", '.+?\x2c\x20|.+$')
               FROM "PEUGEOT_PART" as "PEUGEOT_PART_02"
               WHERE "PEUGEOT_PART_02"."ID" = "PEUGEOT_PART"."ID"
              )) as "MY_TABLE"("MY_OUTPUT_STRING")  -- This is the array name and the name of array column

Villeroy, thanks for your efforts, great job.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[arfgh]

This example assumes the use of HSQL Version 2.x as the database back-end ( NOT the embedded HSQL database ( Version 1.8.0.10 ) ).

yes, embedded hsql database...
I can do this in basic, but sql will be the best way if possible to avoid duplicates and do a fast sorting. In basic, with quicksork and remove duplicates, take some time to process. So i am looking badly for a way to do it in sql... it should be possible by some way...

[Sliderule]

This example assumes the use of HSQL Version 2.x as the database back-end ( NOT the embedded HSQL database ( Version 1.8.0.10 ) ).

yes, embedded hsql database...
I can do this in basic, but sql will be the best way if possible to avoid duplicates and do a fast sorting. In basic, with quicksork and remove duplicates, take some time to process. So i am looking badly for a way to do it in sql... it should be possible by some way...

You have NOT told us the database back-end you are using and you still, have NOT said the database back-end you are using, just:

yes, embedded hsql database...

Therefore, since I am writing this NOT just for you, but, for other forum users reading this post, the answers given might help them.

it should be possible by some way...

It is possible, for example, with HSQL Version 2.x as demonstrated above, or the current version of H2 database back-end.

Sliderule

[arfgh]

yes i did, in the signature,

[RoryOF]

There is still no database information in your .sig.

[arfgh]

OpenOffice last version
that means the database on it. Embedded.

[Villeroy]

You can connect OpenOffice to dozends of different database engines. You can (and should) convert embedded HSQL to external HSQL2 once you leave behind the draft status, particularly when you try to do anything important. I assumed that you are using OpenOffice with HSQL2 because this is what experienced users with higher expectations usually do. If someone with 400 Base related posts does not mention any database engine, I assume that everything will be OK if I dare to offer a solution that works with HSQL2. I did mention that my solution requires HSQL2.