CODEWITHSUNDEEP
sqlpostgresql
hi4ppl
hi4ppl

Reputation: 625

checking value of if it's zero or not with sql

I have a value that comes in column as bellow

{200;0;7713;200;43;7670;200;0;7713

so these value logically divided in three sections... as bellow

200;0;7713

the first digits 200 shows account ID, and the second value which is 0 shows used from that account and the third 7713 value shows remaining, and my goal here is:

if used is greater than zero then pick remaining, like in this example:

all other valued from this account is zero but the the middle one is not so in this case I want to pick value of 7670 as teh value before that is not zero.

the account ID is not fixed means sometimes it will generate 1, 2, 4 or even 4 times like bellow:

{200;25;6713 
{200;0;7713;200;45;9670
{200;0;7713;200;43;8670;200;0;7713
{200;0;7713;200;43;8670;200;0;7713;200;0;7713

Thanks for help

Upvotes: 0

Views: 631

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

You can do this with split_part()

select (case when split_part(col, ';', 2) <> '0' then split_part(col, ';', 3)
             when split_part(col, ';', 5) <> '0' then split_part(col, ';', 6)
             when split_part(col, ';', 8) <> '0' then split_part(col, ';', 9)
             when split_part(col, ';', 11) <> '0' then split_part(col, ';', 12)
        end)
from t;

However, this column suggests that your data structure is broken. You should be storing these values in a separate table. One row with three data values (account, used, and remaining) with repeated rows for an account.

Upvotes: 1

Related Questions