Обсуждение: parsing column info
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
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 > >
--- "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