simplest way to search substring and set flag

Database

simplest way to search substring and set flag

I want to modify a table like this:

ID       List            FLG1   FLG2 

1 000, 111, 123 0 0
2 000, 241, 123 0 0
3 888, 187, 111 0 0
4 424, 000, 123 0 0
5 384, 234, 111 0 0

If List contains substring '000', then set FLG1 to 1 If List contains substring '111', then set FLG2 to 1

So after modefication, it should like this:

ID     |   List     |     FLG1 |  FLG2 

1 000, 111, 123 1 1
2 000, 241, 123 1 0
3 888, 187, 111 0 1
4 424, 000, 123 1 0
5 384, 234, 111 0 1

Please help me.Thank you.

3 REPLIES
Enthusiast

Re: simplest way to search substring and set flag

try with 

select id,list,case when regexp_similar(list,'[^000]+$','i')=0 then 1 else 0 end flag1 fro your_table

and let us see.

Enthusiast

Re: simplest way to search substring and set flag

You can think of this way:

select id,list,case when substr(list,1,3)='000' or substr(list,5,3)='000' or substr(list,9,3)='000' then 1 else 0 end flg1, case when substr(list,1,3)='111' or substr(list,5,3)='111' or substr(list,9,3)='111' then 1 else 0 end flg2 from your_table

Enthusiast

Re: simplest way to search substring and set flag

lserlohn2 ,

its working for me try once .......

select '000, 111, 123' as List , case when POSITION('000' IN  List)>0 then 1 end  FLG1

                                                            ,    case when POSITION('111' IN  List)>0 then 1 end  FLG2    

-- Raj