postgres string compare

Question

I am using a postgres version 8.3 (greenplum). I am trying to compare two tables on a single column called col_name. What I need is a partial string compare on both the column row values. The values are xx.yyy.zzz. I want to pull out the first part namely 'xx' and truncate after that namely '.yyy.zzz'. I only want to compare for two rows for the string till the first period is encountered. There is possibility that the part of the string xx is of varying lengths in characters.

I am using the following logic, but I cant see why it is not working:

select 
distinct x.col_name, 
x.col_num
from table_A x  
left outer join table_b y 
on 
regexp_matches((x.col_name,'^(?:([^.]+)\.?){1}',1),(y.col_name,'^(?:([^.]+)\.?){1}', 1)) 
and x.col_num=y.col_num;

I am getting this error:

ERROR: function regexp_matches(record, record) does not exist LINE 36: regexp_matches((x.col_name,'^(?:([^.]+).?){1}', 1),(y.... ^ HINT: No function matches the given name and argument types. You may need to add explicit type casts. ********** Error **********

ERROR: function regexp_matches(record, record) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You may need to add explicit type casts. Character: 917

Can anyone help me out?

Thanks!

binoternary · Accepted Answer · 2016-07-11 16:57:04Z

1

You can use the split_part function. Split the string to parts using '.' as the delimiter and compare the first components.
See documentation

So your query would be:

select 
  distinct x.col_name, 
  x.col_num
from table_A x  
  left outer join table_b y 
    on split_part(x.col_name, '.', 1) = split_part(y.col_name, '.', 1) 
    and x.col_num=y.col_num;

Your original query produces an error because you give strange parameters to the regexp_matches function.
The signature is regexp_matches(string text, pattern text [, flags text]), but your first argument to it is (x.col_name,'^(?:([^.]+)\.?){1}',1) which is not a string (and the same applies for the second argument)

edited Jul 11, 2016 at 16:57

answered Jul 11, 2016 at 16:17

binoternary

1,9351 gold badge14 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

postgres string compare

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