Cut part of string using PostgreSQL

Question

I have a unique case where I must join two tables based on text field. (Which is horibble I know)

The thing is that the join column in one table is:

ColumnA

Amz-g-r-t
Amz-g-r2-t
Amz-g1-r-t-x

and in the second table the column might contains more values in the same rows:

ColumnB

Amz-g-r-t not to ship
Amz-g-r2-t OK
Amz-g1-r-t-x

Basicly I wanna do:

select *
from A
join B on (a.ColumnA=b.ColumnB)

but it won't work because of the data in B. So I need a way to remove all chars from B rows after the first space. So ColumnB will be:

Amz-g-r-t
Amz-g-r2-t
Amz-g1-r-t-x

How can I do that?

It sounds like everything separate by a - is significant. I would consider breaking them out into their own columns. — Evan Carroll
– Evan Carroll, Commented Dec 15, 2016 at 9:13

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

You can use split_part() function.

select *
from A
join B on a.ColumnA=split_part(b.ColumnB, ' ', 1)

split_part(string text, delimiter text, field int) Split string on delimiter and return the given field (counting from one)

Performance would be poor not only because column are of text type but also because of using function on joining column which makes query Non - SARGABLE

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 15, 2016 at 9:08

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

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

1 Comment

user330315 Over a year ago

You can always create an index on split_part(b.ColumnB, ' ', 1)

Collectives™ on Stack Overflow

Cut part of string using PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related