postgresql 9.5: trying to split a string into two fields based on spaces

Question

I have a field in a postgres table that looks like the table below. I want to split the string into two separate components based upon the space as a delimiter. Note all of the fields are TEXT.

 tablename:  intersection_table_wi
 wwhid   
 -----   

 "102  5" 
 "104 61"
 "103 84"

So I want to convert this to a Target like this:

 wwhid     wv002     wv003
 -----     -----     -----

 "102  5"  "102"     "5"
 "104 61"  "104"     "61"
 "103 84"  "103"     "84"

The problem is that when I write the query I keep getting things that look more like this:

 wwhid     wv002     wv003
 -----     -----     -----

 "102  5"  "102  5"   ""
 "104 61"  "104 61"   ""
 "103 84"  "103 84"   ""

Now the subtle problem is that in some cases there is more than one space between the two substrings in whhid while in other cases there is only one space.

The query I tried are as follow:

UPDATE intersection_table_wi 
SET wv002 = SPLIT_PART(BTRIM(whhid), '/\s+', 1), 
wv003 = SPLIT_PART(BTRIM(whhid), '/\s+', 2);

Can anyone tell me how I can fix this query to obtain the target specified above?

Yeah, these are all strings. That is just how the original data came. I can apply a cast to the variables after they are separated out, but for the sake of the minimal example I just wanted to keep it simple. But the answer is that the wwhid, wv002, wv003 variables are text. — krishnab
– krishnab, Commented May 22, 2017 at 6:12
I think they are single quotes. The only reason I say that is because if I try to use double quotes then I generally get an error about tablename or such--since the double quotes are use for tablenames as well, right. — krishnab
– krishnab, Commented May 22, 2017 at 6:18

user330315 · Accepted Answer · 2017-05-22 06:12:39Z

8

split_part() doesn't support regular expressions. You can only specify a "simple" string as the delimiter.

To split on a regular expression you need regexp_split_to_array()

UPDATE intersection_table_wi 
   SET wv002 = (regexp_split_to_array(BTRIM(whhid), '\s+'))[1], 
       wv003 = (regexp_split_to_array(BTRIM(whhid), '\s+'))[2];

answered May 22, 2017 at 6:12

user330315

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

3 Comments

krishnab Over a year ago

Ahh excellent. This worked. Thanks for the tip. I did not know that split_part did not take regular expressions. I appreciate your help on this.

user330315 Over a year ago

@krishnab: also note that regular expressions are not started with / in Postgres (unless you mean to include the / in the regex as a constant character)

krishnab Over a year ago

Okay, thanks. I get confused on the different regex dialects. I will keep that in mind.

Collectives™ on Stack Overflow

postgresql 9.5: trying to split a string into two fields based on spaces

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related