2 Replies Latest reply: May 15, 2013 6:12 AM by Zama Phumla RSS

    extracting info from a row in a csv.

      Hello there

       

      I have the following problem, i have a table with a column that looks like this

       

           1. BMC Patrol Agent 3.9.00 on srv001601

           2. Red Hat KVM Server 83 running on srv001352.mud.internal.co.za

           3. Microsoft Hyper-V Server 2008 R2 SP1 identified as SRV001543 on srv001502

           4. OdLetters-1.0.17:doc-was7-32-02

       

      The only information I would like to extract from this is the srv_name, eg:

      srv001601

      srv001352

      srv001502

      the rest would be 'other'

       

      This would be loaded as a list box with the srv_name and extra field 'other', where 'other' is the rows without a srv_name in them (e.g: row 4)

      As you can see the srv_name is placed in diferrent instances in the data and you can have more than 1 srv in 1 row of data thus i cannot select text beggining with srv (eg: row3).

       

      What I have noticed is that the srv_name I want is always after the word 'on', if thats any help.

       

      Please help.

        • Re: extracting info from a row in a csv.
          Gysbert Wassenaar

          Try: if(index(MyField, 'on srv'), mid(MyField, index(MyField, 'on srv')+3,9),'other') as ServerName

           

          edit: index returns the start position of the first occurence of a search string in a string. If MyField contains 'on srv' then index(MyField, 'on srv') returns a number larger than 0. In that case we know that there's a server number in the string. If there isn't we specify 'other' as result of the if function. To get the server number the mid function is used. It needs a starting position and a length to determine the substring that's extracted from MyField. The starting position in again calculated with the index function, but we add 3 so we start at srv instead of on.