• State Verified Answer
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 95 subscribers
  • Views 2072 views
  • Users 0 members are here
Options
Related

Search in multi value column on SQL

kinshasa
over 4 years ago

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

  • +1 over 4 years ago

    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.

    • 0 over 4 years ago

      Alternatively, you could use charindex.

      e.g. charindex('b',MVA) <> 0

      That will find rows that have 'b' in the MVA.