current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote 0 down vote favorite

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!

share|improve this question
up vote 1 down vote accepted

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.