Thread: Casting from varchar to numeric

Hi list,

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

Anybody have any ideas?

Thanks

Tom

Tom,

> 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
>
> Anybody have any ideas?

Well, first off, you need to know the PostgreSQL conversion functions.
 See the online documentation:  Functions and Operators --> Formatting
Functions.

Second, if you check Roberto Mello's PL/pgSQL cookbook, you'll notice
that I posted a set of functions which auto-format phone numbers to US
spec.
http://www.brasileiro.net/postgres/cookbook/

It's under "String Manipulation Functions".

-Josh Berkus

> 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

Would this work?

CREATE FUNCTION format_us_phone(varchar)
RETURNS varchar AS '
BEGIN;
SELECT "(" || SUBSTR($1,0,3) || ")" || SUBSTR($1,3,3) || "-" ||
SUBSTR($1,6,4);
END; ' LANGUAGE 'sql';

then..

SELECT format_us_phone(phonefield) FROM...;

Hi all,

ok...so the solution I currently have is to convert the varchar(10) into a
numeric value and then convert it back into a varchar but with all the bits
added.  This is my current code

to_char( to_number(hotel.main_phone, '9999999999') ,'(999)999-9999')

The problem with this is that I am getting a space between the first number
and the first bracket so it looks something like this....

( 999)999-9999

I have tried to get rid of the space using the MI template pattern but nothing
seems to work.

Does anybody have any ideas as to what is going wrong.

Cheers

Tom

On Thursday 16 May 2002 09:48 am, Tom Ansley wrote:
> Hi list,
>
> 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
>
> Anybody have any ideas?
>
> Thanks
>
> Tom
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

> -----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