From: soumitra bhandary <soumitra.bhandary@hotmail.com>
To: Firthouse banu <penguinsfairy@gmail.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Table column with numeric and string values
Date: Mon, 13 Sep 2021 09:17:27 +0000
Message-ID: <MA0PR01MB60063012056B892E851A73FBE0D99@MA0PR01MB6006.INDPRD01.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CAK_Ha4yB_gr7ugb1Xy70y_QvazXz55_fYxvRxzSaneAVSqEUOA@mail.gmail.com>
References: <CAK_Ha4yB_gr7ugb1Xy70y_QvazXz55_fYxvRxzSaneAVSqEUOA@mail.gmail.com>
Hi,
You can try to filter data with some function and function code should be like below .
CREATE OR REPLACE FUNCTION "sys"."isnumeric"(text)
RETURNS "pg_catalog"."bool" AS $BODY$
DECLARE x NUMERIC;
BEGIN
x = $1::NUMERIC;
RETURN TRUE;
EXCEPTION WHEN others THEN
RETURN FALSE;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 100
;
Try this out
Thanks,
Soumitra
Sent from my iPhone
On 13-Sep-2021, at 2:25 PM, Firthouse banu <penguinsfairy@gmail.com> wrote:
Hello everyone,
Need a urgent help.
I have a table sqlt_data with column strungvalue as character varying , in that column we have both numbers and string.
I have function which pulls this values and do round(avg(string value::numeric,2),0) . Am able to run the function fine with numeric values and it is obvious but for string values getting error. How to make this function work with string values . As far as I know avg or round cannot be done on string values. Please advise.
Thanks
Firthouse
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: soumitra.bhandary@hotmail.com, penguinsfairy@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Table column with numeric and string values
In-Reply-To: <MA0PR01MB60063012056B892E851A73FBE0D99@MA0PR01MB6006.INDPRD01.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox