Using split_part and regex for postgres column

Question

I have a column that has both numbers and their units and I am trying to split it into two columns with regex, but I don't know how to use regex with split_part. Can anyone advise?

This is an example column:

|    lot_size1    |
---------+---+-----
| 25,665 Lot SqFt |
| 4,111 Lot SqFt  |
| 14,000 Lot SqFt |
|  Lot SqFt       |
| 40.00 Acres     |

This is a draft of my query:

select split_part(lot_size1, ???,1) as area,
  split_part(lot_size1, ???,2) as units;

Kaushik Nayak · Accepted Answer · 2018-12-05 17:00:58Z

2

use SUSBTRING

select SUBSTRING ( lot_size1 FROM  '^[0-9,.]+' ) as area ,
       SUBSTRING ( lot_size1 FROM  '[^0-9,.]+' ) as units
       FROM t;

'^[0-9,.]+' - matches sequence of numbers , and .

'[^0-9,.]+' - matches sequence of anything other than above.

Demo

answered Dec 5, 2018 at 17:00

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

reallymemorable Over a year ago

Thanks! How would I modify this query to change any empty area fields to NULL?

Kaushik Nayak Over a year ago

@reallymemorable : It is in fact null. Run \pset null '(null)' if you're using psql to see it.

reallymemorable Over a year ago

How would I eliminate the empty space that appears before the units text?

Kaushik Nayak Over a year ago

@reallymemorable : use trim()

Collectives™ on Stack Overflow

Using split_part and regex for postgres column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related