CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3
Johnathan
Johnathan

Reputation: 343

Cut part of string using PostgreSQL

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?

Upvotes: 1

Views: 2333

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28781

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

Upvotes: 6

Related Questions