CODEWITHSUNDEEP
sqlpostgresql
Phil
Phil

Reputation: 109

SQL - Create two new row entries depending on specific column values

I have a databasetable which contains some address data. My Problem is some rows have two housenumber entries, which look like this: 

'8, 8A'
'12,14'

What I want to do, is:

The housenumber column is a textfield, because there can be additions like 'A', 'B', etc. I'd like to know if there is any way to do this in sql, so any help is appreciated.

EDIT

Here is what my table looks like: enter image description here

Upvotes: 0

Views: 515

Answers (3)

Ramesh
Ramesh

Reputation: 1484

You can do this

Data

CREATE TABLE address
(house_number VARCHAR(20),
 street   VARCHAR(100));

INSERT INTO address  VALUES ('8, 8A', 'High Street');
INSERT INTO address VALUES ('12, 14', 'New Street');
INSERT INTO address VALUES ('1', 'First Street');

Solution

SELECT TRIM(adr_split.house_number) AS house_number, adr.street
  FROM address adr, UNNEST(STRING_TO_ARRAY(adr.house_number, ',')) adr_split(house_number);

Output

house_number    street
8               High Street
8A              High Street
12              New Street
14              New Street
1               First Street

In terms of replacing this output with your old data, you can do this

CREATE TABLE new_address
(house_number VARCHAR(20),
 street   VARCHAR(100));

INSERT INTO new_address
SELECT TRIM(adr_split.house_number) AS house_number, adr.street
  FROM address adr, UNNEST(STRING_TO_ARRAY(adr.house_number, ',')) adr_split(house_number);

DROP TABLE address;

ALTER TABLE new_address RENAME TO address;

Now the address table would have the data as you expected.

Output

SELECT * FROM address;

    house_number    street
    8   High Street
    8A  High Street
    12  New Street
    14  New Street
    1   First Street

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51416

without knowledge on all columns its hard to say, but basically idea would be:

prepare:

t=# with a(i,addr) as (values(1,$$'8, 8A'
'12,14'$$))
select * into so4 from a;
SELECT 1

check:

t=# select * from so4;
 i |  addr
---+---------
 1 | '8, 8A'+
   | '12,14'
(1 row)

action:

t=# with a as (select i, unnest(string_to_array(addr,chr(10))) from so4 group by i, addr)
, i as (insert into so4 select * from a)
delete from so4 using a where a.i = so4.i and a.unnest <> so4.addr
;
DELETE 1

check:

t=# select * from so4;
 i |  addr
---+---------
 1 | '8, 8A'
 1 | '12,14'
(2 rows)

Upvotes: 0

Steef
Steef

Reputation: 323

How about:

select left(col_1, charindex(',', col_1))
  from table_1
 where charindex(',', col_1) > 0
 union 
select substring(col_1, charindex(',', col_1), 255)
  from table_1
 where charindex(',', col_1) > 0
 union 
select col_1
  from table_1
 where charindex(',', col_1) = 0

Upvotes: 0

Related Questions