Thread: Divide table raw into chunks

Divide table raw into chunks

From

Shmagi Kavtaradze

Date:

28 February 2016, 14:39:19

I have a table with too long entries with 1s and 0s. For example I have table :

| Sent id.|   BoW.   |
|---------|----------|
|    1    | 10100101 |
|    2    | 00011110 |
|    3    | 10101111 |

I want to create new table which will divide column BoW. entries into some arbitrary length and assigns chunk number.

| Sent id.| Chunk No. | BoW. |
|---------|-----------|------|
|    1    |     1     | 1010 |
|    1    |     2     | 0101 |
|    2    |     1     | 0001 |
|    2    |     2     | 1110 |
|    3    |     1     | 1010 |
|    3    |     2     | 1111 |

I am beginner, tried to search in documentation, but without success. Maybe something like this, but with proper function:

select b.sent_id, chunk, substring(bow from (chunk - 1) * 4 + 1 for 4)
from (select b.sent_id, generate_series(1, 1 + floor(length(bow) - 1 / 4) ) as chunk     from bow1 b    ) b;

I have 4623 Sent ids (sentences) and each is 10,000 characters long. I want to divide them into 32-40 character long chunks.

Re: Divide table raw into chunks

From

"Miguel Beltran R."

Date:

04 March 2016, 01:49:55

I not sure but proably with a recursive CTE (common table expression)

somethink like

with R as (

select send_id, 4 as len, 1 as chunk
, substring(bow from 1 for 4) as bow_ok, substring (bow for 4 +1) as bow_2

from bow1

union all

select send_id, len, chunk+1 as chunk

, substring(bow_2 from 1 for 4) as bow_ok, substring (bow from 4 +1) as bow_2

where char_length((bow_2) >0

from R

)

select * from r

www.postgresql.org/docs/9.3/static/queries-with.html

2016-02-28 5:39 GMT-06:00 Shmagi Kavtaradze <kavtaradze.s@gmail.com>:

I have a table with too long entries with 1s and 0s. For example I have table :

| Sent id.|   BoW.   |
|---------|----------|
|    1    | 10100101 |
|    2    | 00011110 |
|    3    | 10101111 |

I want to create new table which will divide column BoW. entries into some arbitrary length and assigns chunk number.

| Sent id.| Chunk No. | BoW. |
|---------|-----------|------|
|    1    |     1     | 1010 |
|    1    |     2     | 0101 |
|    2    |     1     | 0001 |
|    2    |     2     | 1110 |
|    3    |     1     | 1010 |
|    3    |     2     | 1111 |

I am beginner, tried to search in documentation, but without success. Maybe something like this, but with proper function:

select b.sent_id, chunk, substring(bow from (chunk - 1) * 4 + 1 for 4)
from (select b.sent_id, generate_series(1, 1 + floor(length(bow) - 1 / 4) ) as chunk     from bow1 b    ) b;

I have 4623 Sent ids (sentences) and each is 10,000 characters long. I want to divide them into 32-40 character long chunks.

________________________________________
Lo bueno de vivir un dia mas
es saber que nos queda un dia menos de vida

Re: Divide table raw into chunks

From

"David G. Johnston"

Date:

04 March 2016, 02:20:21

I have 4623 Sent ids (sentences) and each is 10,000 characters long. I want to divide them into 32-40 character long chunks.

I suppose replace {1,5} with {32,40} should work...the question is what is so special about 32-40 and why a range instead of just saying 40? Does it really matter if the final group is less than 32 (assuming every other group was 40)?

SELECT regexp_matches[1] FROM regexp_matches('0123456789012','(.{1,5})','g')

Alter to taste.

David J.