How split multiple strings in a column

Creating tables and queries

How split multiple strings in a column

Postby arfgh » Thu Jan 16, 2020 12:53 am

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   Expand viewCollapse view
   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 ?
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP Pro x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 502
Joined: Tue Mar 05, 2013 6:44 pm

Re: How split multiple strings in a column

Postby Villeroy » Thu Jan 16, 2020 1:03 pm

You really have a field of strings like "abc0cdef0g0hijk" with a varying count of elements?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How split multiple strings in a column

Postby arfgh » Thu Jan 16, 2020 3:45 pm

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   Expand viewCollapse view
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.
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP Pro x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 502
Joined: Tue Mar 05, 2013 6:44 pm

Re: How split multiple strings in a column

Postby MrProgrammer » Thu Jan 16, 2020 6:16 pm

arfgh wrote:Is there a way to split that string in a column without [knowing] the number of splits and rows?
arfgh wrote:Imagine the follow string, where the '*' is the character 0 hex
If you don't find a solution with SQL, use a query to copy the column to Calc, change the delimiter from CHAR(0) to CHAR(13), run Text to Columns to split the cells into multiple rows, and copy the data back to a Base table. This might take about ten minutes, depending on your skills with Base and Calc. You might be able to save a step by changing the delimiter in the query instead of doing that in Calc.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3953
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How split multiple strings in a column

Postby arfgh » Fri Jan 17, 2020 12:21 am

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 ?
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP Pro x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 502
Joined: Tue Mar 05, 2013 6:44 pm

Re: How split multiple strings in a column

Postby Villeroy » Sat Jan 18, 2020 3:29 pm

Code: Select all   Expand viewCollapse view
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'
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How split multiple strings in a column

Postby arfgh » Sun Jan 19, 2020 2:51 pm

no, that wasnt what i want...
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP Pro x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 502
Joined: Tue Mar 05, 2013 6:44 pm

Re: How split multiple strings in a column

Postby Villeroy » Sun Jan 19, 2020 4:14 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How split multiple strings in a column

Postby arfgh » Mon Jan 20, 2020 5:11 pm

Code: Select all   Expand viewCollapse view
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.
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP Pro x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 502
Joined: Tue Mar 05, 2013 6:44 pm

Re: How split multiple strings in a column

Postby Sliderule » Tue Jan 21, 2020 1:19 am

arfgh wrote:no, that wasnt what i want...

arfgh wrote:
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
-- 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. :super: :bravo: :bravo:

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.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: How split multiple strings in a column

Postby arfgh » Tue Jan 21, 2020 2:30 am

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...
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP Pro x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 502
Joined: Tue Mar 05, 2013 6:44 pm

Re: How split multiple strings in a column

Postby Sliderule » Tue Jan 21, 2020 2:43 am

arfgh wrote: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:

arfgh wrote: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.

arfgh wrote: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
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: How split multiple strings in a column

Postby arfgh » Tue Jan 21, 2020 11:48 am

yes i did, in the signature,
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP Pro x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 502
Joined: Tue Mar 05, 2013 6:44 pm

Re: How split multiple strings in a column

Postby RoryOF » Tue Jan 21, 2020 3:17 pm

There is still no database information in your .sig.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 30435
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How split multiple strings in a column

Postby arfgh » Wed Jan 22, 2020 9:57 pm

OpenOffice last version
that means the database on it. Embedded.
OpenOffice last version | Mageia Linux x64 | Windows 8.1 Enterprise x64 | Windows XP Pro x64 SP2 | Java 1.8.0_231
arfgh
 
Posts: 502
Joined: Tue Mar 05, 2013 6:44 pm

Re: How split multiple strings in a column

Postby Villeroy » Wed Jan 22, 2020 11:07 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests