pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Table column with numeric and string values
3+ messages / 3 participants
[nested] [flat]

* Table column with numeric and string values
@ 2021-09-13 08:54  Firthouse banu <penguinsfairy@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Firthouse banu @ 2021-09-13 08:54 UTC (permalink / raw)
  To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Table column with numeric and string values
@ 2021-09-13 09:17  soumitra bhandary <soumitra.bhandary@hotmail.com>
  parent: Firthouse banu <penguinsfairy@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: soumitra bhandary @ 2021-09-13 09:17 UTC (permalink / raw)
  To: Firthouse banu <penguinsfairy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>

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


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Table column with numeric and string values
@ 2021-09-13 10:43  pramod kg <pramod11287@gmail.com>
  parent: soumitra bhandary <soumitra.bhandary@hotmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: pramod kg @ 2021-09-13 10:43 UTC (permalink / raw)
  To: soumitra bhandary <soumitra.bhandary@hotmail.com>; +Cc: Firthouse banu <penguinsfairy@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>

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
>
>

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2021-09-13 10:43 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-13 08:54 Table column with numeric and string values Firthouse banu <penguinsfairy@gmail.com>
2021-09-13 09:17 ` soumitra bhandary <soumitra.bhandary@hotmail.com>
2021-09-13 10:43   ` pramod kg <pramod11287@gmail.com>

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