CODEWITHSUNDEEP
regexpostgresql
wizengeeky
wizengeeky

Reputation: 67

postgres string compare

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!

Upvotes: 0

Views: 699

Answers (1)

binoternary
binoternary

Reputation: 1915

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)

Upvotes: 1

Related Questions