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

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
– Stephen Denne, Commented Mar 29, 2010 at 2:41
My bad... You'd think the WARNING would have tipped me off... — n00b0101
– n00b0101, Commented Mar 29, 2010 at 8:59

Stephen Denne · Accepted Answer · 2010-03-29 03:15:10Z

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;

edited Mar 29, 2010 at 3:15

answered Mar 29, 2010 at 2:33

Stephen Denne

37.2k10 gold badges49 silver badges61 bronze badges

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

2 Comments

n00b0101 Over a year ago

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 Over a year ago

Thank you again. I'm a bit out of my depth with pg, so I appreciate the guidance

Collectives™ on Stack Overflow

postgres - regex_replace in distinct clause?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related