Обсуждение: Table column with numeric and string values
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
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.ThanksFirthouse
You can use regex with CASE in your select query:
select value, case when value ~ '\d' then (round(value::numeric)::varchar) else (value::varchar) end from sqlt_data;
Verify the efficiency of the query before implementing it.
On Mon, Sep 13, 2021 at 2:47 PM soumitra bhandary <soumitra.bhandary@hotmail.com> wrote:
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,SoumitraSent from my iPhoneOn 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.ThanksFirthouse