Postgres SQL - Split string at Deliminator and replace with substring

Question

My goal is to take the ad_name strings and (1) split sting at the '-' deliminator and then replace that substring with the correct term with a space. so "peelpads" turns into "peel pads", "gtox" turns into "g tox", "meljeans" turns into "mel jeans", and "alexisskirt" turns into "alexis skirt."

CREATE TABLE facebook_campaigns
(campaign varchar(255),
ad_name varchar(255),
Media_spend numeric(38,6),
day date);

INSERT INTO facebook_campaigns VALUES
('Peel Pads','retargeting-peelpads-201907', 1000),
('Peel Pads','prospecting-peelpads-201907', 3000),
('Peel Pads','prospecting-peelpads-201906', 2000),
('G Tox','prospecting-gtox-201907', 1000),
('G Tox','retargeting-gtox-201907', 1000),
('Pre Fall','prospecting-meljeans-201907', 1000),
('Pre Fall','retargeting-meljeans-201907', 500),
('Pre Fall','retargeting-alexisskirt-201907', 1500),
('Pre Fall','prospecting-alexisskirt-201907', 2000);

I'm able to separate the column by the deliminator but I'm unsure how to isolate that and add the space in the term. This is what I have done so far.

SELECT 
    ad_name,
    regexp_replace('retargeting-peelpads-201907',
                   'peelpads', 'peel pads'),
    SPLIT_PART(ad_name, '-', 2) as product
FROM facebook_campaigns

and my result is

retargeting-peelpads-201907 | retargeting-peel pads-201907  | peelpads
prospecting-peelpads-201907 |   retargeting-peel pads-201907| peelpads

I really just want a column that results to

peel pads

I just want a column that says "peel pads" because in the end my goal is to combine that column with the "product_name" column in the table below.

CREATE TABLE order_line_items
(order_line_item_id bigint,
order_id bigint,
product_name varchar(255),
business_unit varchar(255),
source varchar(255));


INSERT INTO order_line_items VALUES
(1, 1,'peel pads','Bananadrinks','facebook'),
(2, 2,'peel pads','Bananadrinks','organic'),
(3, 2,'mel jeans','Redsticks','organic'),
(4, 2,'g tox','Bananadrinks','facebook'),
(5, 3,'alexis skirt','Redsticks','email'),
(6, 4,'alexis skirt','Redsticks','facebook'),
(7, 5,'g tox','Bananadrinks','facebook'),
(8, 5,'mel jeans','Redsticks','facebook'),
(9, 6,'mel jeans','Redsticks','email');
Text to DDL

How do you know what the correct term is? And I'm confused. The first column seems to have what you want. — Gordon Linoff
– Gordon Linoff, Commented Feb 12, 2020 at 21:45
@GordonLinoff I know I want the term in the middle with the space in between. I want something similar to the third column except instead of "peelpads" I want it to say "peel pads". The next step for me after this is to use that term to connect to another table that is why I need the terms isolated — lydol
– lydol, Commented Feb 12, 2020 at 21:50
How do you know (from the input) where the space should be added? — user330315
– user330315, Commented Feb 12, 2020 at 21:53
@a_horse_with_no_name This table will eventually connect to another table with a 'product name' label which is that second term in the ad_name substring but with a space. — lydol
– lydol, Commented Feb 12, 2020 at 21:59
@lydol: you should show this other table and explain your purpose. Presumably, you could remove spaces in values coming from the other table and then join. — GMB
– GMB, Commented Feb 12, 2020 at 22:02

GMB · Accepted Answer · 2020-02-13 00:47:32Z

1

There is an obvious match on column facebook_campaigns(campaign), but I assumed this is not what you expect.

To join both tables, it looks like it would be sufficient to remove the spaces from the order_line_items table, surround the value with hyphens (-), and then do pattern matching against column ad_name in facebook_campaigns:

select ...
from facebook_campaigns fc
inner join order_line_items oli
    on fc.ad_name like '%-' || replace(oli.product_name, ' ', '') || '-%'

answered Feb 13, 2020 at 0:47

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Postgres SQL - Split string at Deliminator and replace with substring

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related