Обсуждение: Table column with numeric and string values

Поиск
Список
Период
Сортировка

Table column with numeric and string values

От
Firthouse banu
Дата:
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 

Re: Table column with numeric and string values

От
soumitra bhandary
Дата:
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 

Re: Table column with numeric and string values

От
pramod kg
Дата:
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,
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