All Forums Database
lserlohn2 1 post Joined 10/14
08 Oct 2014
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.
 

Raja_KT 1246 posts Joined 07/09
08 Oct 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Raja_KT 1246 posts Joined 07/09
08 Oct 2014

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
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

09 Oct 2014

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
 
 

You must sign in to leave a comment.