postgres - regex_replace in distinct clause?

Question

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*', ''...

Answer 1

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;