From: | pramod kg <pramod11287(at)gmail(dot)com> |
---|---|
To: | soumitra bhandary <soumitra(dot)bhandary(at)hotmail(dot)com> |
Cc: | Firthouse banu <penguinsfairy(at)gmail(dot)com>, "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 10:43:06 |
Message-ID: | CAHkcXnwhFtC-7dJC5K6bNJYL83hDZUTzO3E4YwGg0CrfoprB=w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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(dot)bhandary(at)hotmail(dot)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(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 | Avinash Vallarapu | 2021-09-13 11:39:05 | Re: autoovacuum launcher process |
Previous Message | soumitra bhandary | 2021-09-13 09:17:27 | Re: Table column with numeric and string values |