Re: Group by on %like%

Hi,

Le 3 juil. 09 à 11:44, Jennifer Trey a écrit :
> I would like to run a query and group several rows based on a phone
> number.
> However, the same phone number might have a prefix on occasion,
> example :
>
> name | phone_number
> ----------------------
> james | 123456
> james | 00441234556
> as you can see, the first 2 James seems to belong together.

What I would do is provide a normalize_phone_number(phone_number
text), such as it returns the same phone number when given a number
with or without international prefix.

Then you
SELECT name, normalize_phone_number(phone_numer)
FROM relation
GROUP BY 1, 2;

Now you're left with deciding if you prefer to normalize with the
prefix or with it stripped, and to invent an automated way to detect
international prefixes. The so called prefix project might help you do
this if you have a table of known prefixes to strip (or recognize):
http://prefix.projects.postgresql.org/
http://prefix.projects.postgresql.org/prefix-1.0~rc1.tar.gz

CREATE OR REPLACE FUNCTION normalize_phone_number(text)
RETURNS text
LANGUAGE PLpgSQL
STABLE
AS $f$
DECLARE
v_prefix text;
BEGIN
SELECT prefix
INTO v_prefix
FROM international_prefixes
WHERE prefix @> $1;

IF FOUND
THEN
-- we strip the prefix to normalize the phone number
RETURN substring($1 from length(v_prefix));
ELSE
RETURN $1;
END IF;
END;
$f$;

Note: I typed the function definition directly into the Mail composer,
bugs are yours :)

Regards,
--
dim