Re: Casting from varchar to numeric

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Ansley
> Sent: Thursday, May 16, 2002 11:49 AM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Casting from varchar to numeric
>
> Is there a way of casting from a varchar to a numeric type?
>
> My problem consists of a varchar(10) that are all numeric.  They
> are telephone
> numbers and I want to convert them during a select statement to (XXX)
> XXX-XXXX

Casting from VARCHAR to NUMERIC? Sounds like you want to go from a VARCHAR
containing only digits to a formatted VARCHAR, right? From "2025551212" ->
"(202) 555-1212"

CREATE TABLE Phones (p VARCHAR(10));

INSERT INTO Phones VALUES ('2025551212');

SELECT '(' || SUBSTRING(p FROM 1 FOR 3) || ') ' ||
       SUBSTRING(p FROM 4 FOR 3) || '-' || SUBSTRING(p FROM 7) FROM Phones;

Tho I would make a plpgsql function for the phone display, making it easier
to re-use:

CREATE OR REPLACE FUNCTION to_phone(VARCHAR) RETURNS VARCHAR AS '
DECLARE
  p ALIAS FOR $1;
BEGIN
  RETURN ''('' || SUBSTRING(p FROM 1 FOR 3) || '') '' ||
       SUBSTRING(p FROM 4 FOR 3) || ''-'' || SUBSTRING(p FROM 7);
END;'
LANGUAGE plpgsql WITH (isCachable);

This way you can simply say SELECT to_phone(p) FROM Phones, plus you can
create an index on to_phone(p), so that, if you want to see phones formatted
this way often, it'll be much quicker.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant