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

Answers


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.


Need Your Help

Uploading image to Facebook by using Curl PHP

php image facebook curl upload

I read the instructions: Upload Photo To Album with Facebook&#39;s Graph API

with protractor how to setup internet explorer configuration?

windows angularjs internet-explorer protractor

I am using protractor 1.3.1 and running iedriverserver.exe 2.43.0.0 with IE11 installed (windows).