Postgres Pro
Downloads
Home   >   mailing lists

Thread: parsing column info

parsing column info

From
"Johnson, Shaunn"
Date:

Running PostgreSQL 7.2.1 on RedHat Linux 2.4.7-10.

This might wind up being a perl hack, but I'll ask ...

I have a table that has been imported from someplace
(probably MS Access).  The columns on the table has
been defined as character varying(50).  One of the
columns is "supposed" to be defined as having a decimal.

I no longer want the decimals in that column.  Is there
a way to parse out the decimals when copying this into
a new table?  I was thinking that I can just create the
new table schema, select * from t_table and somewhere
figure out how to parse out the silly '.' from the column
(perhaps in a case statement?).

Any ideas?

Thanks!

-X

Re: parsing column info

From
Don Isgitt
Date:
Hi Shaunn,

It is not aesthetically pleasing, but you could use substr and strpos to
extract up to the unwanted character, similarly to extract from the
unwanted character, and then concatenate the two strings.

Don

Johnson, Shaunn wrote:

> Running PostgreSQL 7.2.1 on RedHat Linux 2.4.7-10.
>
> This might wind up being a perl hack, but I'll ask ...
>
> I have a table that has been imported from someplace
> (probably MS Access).  The columns on the table has
> been defined as character varying(50).  One of the
> columns is "supposed" to be defined as having a decimal.
>
> I no longer want the decimals in that column.  Is there
> a way to parse out the decimals when copying this into
> a new table?  I was thinking that I can just create the
> new table schema, select * from t_table and somewhere
> figure out how to parse out the silly '.' from the column
> (perhaps in a case statement?).
>
> Any ideas?
>
> Thanks!
>
> -X
>
>

Re: parsing column info

From
Jeff Eckermann
Date:
--- "Johnson, Shaunn" <SJohnson6@bcbsm.com> wrote:
> I no longer want the decimals in that column.  Is
> there
> a way to parse out the decimals when copying this
> into
> a new table?

Assuming that this column is a varchar(50) like the
others, and that all you want is to delete the decimal
points, probably the easiest way is to use the
"translate" builtin function:
SELECT translate(field,'.', '') (I think: check the
syntax under "string functions" in the docs).

__________________________________________________
Yahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost
http://dir.remember.yahoo.com/tribute