postgres - regex_replace in distinct clause?
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 开发者_Python百科a string literal
LINE 1: ...CT COUNT ( DISTINCT mid, regexp_replace(na_fname, '\\s*', ''...
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;
精彩评论