From: | soumitra bhandary <soumitra(dot)bhandary(at)hotmail(dot)com> |
---|---|
To: | Firthouse banu <penguinsfairy(at)gmail(dot)com> |
Cc: | "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Table column with numeric and string values |
Date: | 2021-09-13 09:17:27 |
Message-ID: | MA0PR01MB60063012056B892E851A73FBE0D99@MA0PR01MB6006.INDPRD01.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | pramod kg | 2021-09-13 10:43:06 | Re: Table column with numeric and string values |
Previous Message | Firthouse banu | 2021-09-13 08:54:41 | Table column with numeric and string values |