Search in multi value column on SQL

Question

Hello Experts, 
 I have a char(7) separated custom multi value column in ADSGroup. On a SQL level, I'm trying to get all rows which contain a particular MV attribute 
 For instance, if my row1 has values a;b;c and row2 has value c;d;e, then if I search for c I should get both row1 and row2, similarly if I search for 'b' I should get only row1 
 I believe there are some QBM_FCV functions but I'm not sure how to use those, any hints? 
 Thanks 
 Kin

Barry Jackson · Accepted Answer

Hi Kin, 
 If you have this type of data: 
 UID_ADSGroup MVA 
 aaaaaa a;b;c;d 
 bbbbbb c;d;e;f 
 (where the ; is actually the char(7) MVA delimeter) 
 You can convert it to this using the below SQL: 
 UID_ADSGroup ParameterValue 
 aaaaaa a 
 aaaaaa b 
 aaaaaa c 
 aaaaaa d 
 bbbbbb c 
 bbbbbb d 
 bbbbbb e 
 bbbbbb f 
 
 Of course you should only need the select. The declares/inserts are just to set up some test data. 
 That should get you started. 
 HTH, Barry.

Barry Jackson · Answer

Alternatively, you could use charindex. 
 e.g. charindex('b',MVA) <> 0 
 That will find rows that have 'b' in the MVA.

Search in multi value column on SQL

Top Replies