How can I slipt colum called SOMETHING if it has got values
"british, born in England 19202013"
into four columns:
british, England, 1920, 2013
Add a comment
|
2 Answers
Consider this example. You can replace src.something with your actual field.
UPDATE
Updated query to allow for "missing data" within that source string. This also allow the content to be in various different positions, eg "British, born in England 1920" and "British ,born in England 19202013" will work too. It is quite flexible and tolerant to dirty-data.
Please mark as answer if this solves your question. TQ.
SELECT substring( src.something from '(\S+)\s*,') AS citizenship
, substring( src.something from 'born\s+in\s+(\S+)') AS country
, substring( src.something from '(\d{4})(\d{4})?') AS fromyear
, substring( src.something from '\d{4}(\d{4})') AS toyear
FROM (
SELECT 'british, born in England 19202013'::TEXT AS something
) AS src;
It will return the following result:
WITH t(something) AS ( VALUES
('british, born in England 19202013'::TEXT)
)
SELECT
split_part(s[1],',',1) AS col1,
s[4] AS col2,
substr(s[5],1,4) AS col3,
substr(s[5],5) AS col4
FROM t,
regexp_split_to_array(t.something,E'\\s+') s;
Result:
col1 | col2 | col3 | col4
---------+---------+------+------
british | England | 1920 | 2013
(1 row)
UPDATE
In order to populate these values in your table you need to alter table 1st and than perform update:
-- add columns
ALTER TABLE t
ADD COLUMN col1 TEXT,
ADD COLUMN col2 TEXT,
ADD COLUMN col3 INTEGER,
ADD COLUMN col4 INTEGER;
-- perform update
WITH splitted_data AS (
SELECT
split_part(s[1],',',1) AS col1,
s[4] AS col2,
substr(s[5],1,4) AS col3,
substr(s[5],5) AS col4,
something
FROM t,
regexp_split_to_array(t.something,E'\\s+') s
)
UPDATE t SET
col1=sd.col1,
col2=sd.col2,
col3=sd.col3::INTEGER,
col4=sd.col4::INTEGER
FROM (
SELECT col1,col2,col3,col4,something FROM splitted_data
) AS sd
WHERE t.something = sd.something;
3 Comments
Mateusz
ok, thank you. And how to do it on a table in database? I mean to update it? remove old column and paste 4 new columns?
Dzmitry Savinkou
updated the answer @Mateusz instead of using
col1 etc you can adjust this example to your data modelMateusz
ok thank you again! but actualy it doesnt work when some of patterns is missed for eg a date