Split text column into 2 using regexp_split_to_array

Question

I have a table in DB. http://sqlfiddle.com/#!15/48214/1 I need to split dump column into 2 - user ID and campaign number. I have been done this using follow code:

select
substring (dump, ((position('u' in dump))+1),(length(dump)-14)) as user_id, 
substring (dump, ((position('p' in dump))+3),1)) as campaign_n  from "User_source";

But I need to do it using regex method in one code-row. Already studied this page http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP, but I'm a little bit confused with how to use correctly all this atoms and quantifiers for this particular task. Will be grateful for any kind of help and explanation.

Where is the Regex code that you have tried?

Aminah Nuraini
– Aminah Nuraini

2016-04-23 22:41:24 +00:00
Commented Apr 23, 2016 at 22:41 — Aminah Nuraini
– Aminah Nuraini, Commented Apr 23, 2016 at 22:41

Jorge Campos · Accepted Answer · 2016-04-23 22:42:26Z

1

Since you already have every thing in rows you don't need to use regexp_split_to_array. You can do as this:

select regexp_replace("dump", '.+_u([0-9]+).+', '\1') user_id,
       regexp_replace("dump", '.+_cpn_([0-9]+)(.+)?', '\1') cpn
from "User_source";

Meaning

'.+_u([0-9]+).+' anything until _u, then _u, any number one or many times, anything after it. The 'any number one or many times' is grouped so I can left it on the replace \1
'.+_cpn_([0-9]+)(.+)?' anything until _cpn_, any number one or many times, anything after it if exists. The 'any number one or many times' is grouped so I can left it on the replace \1

See it working here: http://sqlfiddle.com/#!15/48214/7

answered Apr 23, 2016 at 22:42

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

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

5 Comments

Фанки Джаз Over a year ago

Thank you Jorge. Yeah, it works. Can I use like two or more patterns in regexp_split_to_array? Or only one. I alredy put you a +, but I don't earn this 15 reputation points, so nobody can't see it

Jorge Campos Over a year ago

AFAIK you can only use one pattern on each regex function. Although you can make a pattern to do more than one thing depending on what would it be of course. If it works and it is the appropriate answer you can only accept it because of your reputation. If it is not the answer, please describe on your question why you need specifically use the regexp_split_to_array. I will update my answer.

Фанки Джаз Over a year ago

I already mentioned in question, that I need like only one row of code, but your answer is correct. I was thiknig that I can accomplish that by using something like this: select a[1], a[2] from (select regexp_split_to_array (dump, '........') from "User_source") as dt(a);

Jorge Campos Over a year ago

Is that some kind of assignment? Makes no sense you say that you need only one line of code as select a[1], a[2] that has two columns from a table created by the regex split function. Problem is that you don't need the split because your data is already splitted in rows and I gave you the same format just with two functions either way is only one line of code it is just one select command. The regex split function would only make sense if the dump field had ALL lines into one piece of text. Since you have each dump as a different row you don't need to split it at all.

Фанки Джаз Over a year ago

Yeah, you answer is correct. Thank you for help and explanation))

Collectives™ on Stack Overflow

Split text column into 2 using regexp_split_to_array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related