Split column into columns using split_part returning empty

Question

I have a column stored as text in postgres 9.6. It's an address and some of the rows have format like BUILD NAME, 40

I saw this answer which looks like what i want, if i run;

select split_part('BUILD NAME, 40', ',', 2) as address_bldgno

It returns; 40 as I want.

When i try;

select 
split_part(address, ', ', 1) as address_bldgname,
split_part(address, ', ', 2) as address_bldgno
from table;

It runs but returns empty values

Related later answer with detailed explanation: https://stackoverflow.com/a/45770999/939860 — Erwin Brandstetter, Aug 19 '17 at 12:42

score 1 · Answer 1 · answered Jul 21 '17 at 21:32

As you mentioned:

...some of the rows have format...

I suspect anything that doesn't contain comma returns empty values. That is due to the split_part condition does not find a match for the argument.

To illustrate how different address values are split here's some example:

WITH    "table" ( address ) AS (
            VALUES
                ( 'BUILD NAME, 40' ),   -- first and second have value
                ( 'BUILD NAME' ),       -- only first have value (bldgname)
                ( '40' ),               -- only first have value (bldgname)
                ( ', 40' ),             -- first is empty string, second is value
                ( 'BUILD NAME,' ),      -- first is value, second is empty string
                ( NULL ),               -- both are NULL
                ( '' )                  -- no match found, both are empty strings
        )
SELECT  split_part( address, ', ', 1 ) as address_bldgname,
        split_part( address, ', ', 2 ) as address_bldgno
FROM    "table";


-- This is how it looks in result:
 address_bldgname | address_bldgno
------------------+----------------
 BUILD NAME       | 40
 BUILD NAME       |
 40               |
                  | 40
 BUILD NAME,      |
                  |
                  |
(7 rows)

If you want to set some defaults for NULL and empty string I recommend to read also: this answer

I don't understand what is happening here, in your example your results are what i want. If i insert my column and table names i get no results. — mapping dom, Jul 22 '17 at 07:34
Sure you are not adding any other conditions in your query? Maybe share exact table description and exact query with execution plan as I think issue then you have is somewhere else than in `split_part` function. — Kristo Mägi, Jul 22 '17 at 19:36

mapping dom · Answer 2 · 2017-07-22T07:34:26.813

0

Answer found here; PostgreSQL 9.3: Split one column into multiple

Should have been;

select somecol 
,split_part(address, ', ', 1) as address_bldgname
,split_part(address, ', ', 2) as address_bldgno
from table;

By adding another column to the select I get all the values of that column back, and the split_part() results where they exist.

edited Jul 22 '17 at 07:34

answered Jul 21 '17 at 20:51

mapping dom

1,737
4
27
50

1

Sorry to say, but this really doesn't answer the question raised. The solution provided in your answer is exactly the same thing already said in the question that was tried. You just repeated that with different syntax :) – Kristo Mägi Jul 21 '17 at 21:36
It worked for me by selecting another column `somecol` the results are as i would expect ie i get a name split from a number, added explanation in my answer to this affect – mapping dom Jul 22 '17 at 07:35

Split column into columns using split_part returning empty

2 Answers2