up vote 0 down vote favorite

Ok... changing the question here... I'm getting an error when I try this:

SELECT COUNT ( DISTINCT mid, regexp_replace(na_fname, '\\s*', '', 'g'), regexp_replace(na_lname, '\\s*', '', 'g'))
FROM masterfile;

Is it possible to use regexp in a distinct clause like this?

The error is this:

WARNING:  nonstandard use of \\ in a string literal
LINE 1: ...CT COUNT ( DISTINCT mid, regexp_replace(na_fname, '\\s*', ''...
link|flag
75% accept rate
That's a warning, not an error. To indicate that a string literal is using \ to escape special characters, use an E before the string literal. – Stephen Denne Mar 29 at 2:41
My bad... You'd think the WARNING would have tipped me off... – n00b0101 Mar 29 at 8:59

1 Answer

up vote 0 down vote accepted 
select trim(regexp_replace(E'\tfoo  \t bar  baz   ', E'\\s+', ' ', 'g'))

replaces all (due to the 'g' flag) whitespace (\s) sequences (+) with a single space, then trims it, returning:

"foo bar baz"

The E is to indicate that the \ escape encoding is used.

With your new, edited question, you're probably looking for a query along the lines of:

select count(*) from (
    select distinct 
        mid, 
        regexp_replace(na_fname, E'\\s*', '', 'g'), 
        regexp_replace(na_lname, E'\\s*', '', 'g') 
    from masterfile) as subquery;
link|flag
Thanks for this... I did figure this part out... I'm trying to do a count distinct (I edited my original question). Any suggestions on how to do that? I tried doing it as a select count(*) where mid in ( select count ( distinct ... ), but I get "subquery has too many columns"... – n00b0101 Mar 29 at 2:41
Thank you again. I'm a bit out of my depth with pg, so I appreciate the guidance – n00b0101 Mar 29 at 8:58

Your Answer

 
or
never shown

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