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