How can I split a string to x number columns and y number of rows?

user_18763992 0
Viewed 65

I have a string looks like 'ab bc 123 cd de ef 232' and I need to split this to look like :

col1 col2 col3
ab bc 123
cd de ef 232

Numbers have to be in the first column, the last string before the numbers has to be in the second column and all characters before that has to be in the first column.

I am working on PostreSQL and have no idea how to do that

postgresql split
1 Answers

step-by-step demo: db<>fiddle

You can use regular expressions to split your strings:

regexp_match(mystring,'^(.+)\s(.+)\s(\d+)\s(.+)\s(.+)\s(\d+)$')

(see how the RegExp works: demo: regex101)

This results in an array of strings you expect. This array can be used to fill your table:

WITH textblocks AS (      -- 1
    SELECT
        regexp_match(mystring,'^(.+)\s(.+)\s(\d+)\s(.+)\s(.+)\s(\d+)$') AS r
    FROM mytable1
)
INSERT INTO mytable2 (col1, col2, col3)

SELECT
    r[1], r[2], r[3]
FROM textblocks

UNION                     -- 2

SELECT
    r[4], r[5], r[6]
FROM textblocks
  1. Execute the RegExp which splits the original string into a text array
  2. Create two records from the text array and insert it into your table
user_39842210
Related