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@xxxxxxxxxxx> 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,Soumitra
Sent from my iPhone
On 13-Sep-2021, at 2:25 PM, Firthouse banu <penguinsfairy@xxxxxxxxx> 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