Returning the string between the 5th and 6th Space

问题:

I have a column of strings that look like this:

Target Host: dcmxxxxxxc032.erc.nam.fm.com Target Name: dxxxxxxgsc047.erc.nam.fm.com Filesystem /u01 has 4.98% available space - fallen below warning (20) or critical (5) threshold.

The column name is [Description]

The substring I would like returned is (dxxxxxxgsc047.erc.nam.fm.com)

The only consistency in this data is that the desired string occurs between the 5th and 6th occurrences of spaces " " in the string, and after the phrase "Target Name: " The length of the substring varies, but it always ends in another " ", hence my attempt to grab the substring between the 5th and 6th spaces.

I have tried

MID([Description],((FIND([Description],"Target Name: "))+13),FIND([Description]," ",((FIND([Description],"Target Name"))+14)))

But that does not work.

(Edit: We use Tableau 8.2, the Tableau 9 only functions can't be part of the solution, thanks though!)

Thank you in advance for your help.

回答1:

In Tableau 9 you can use regular expressions in formulas, it makes the task simpler:

REGEXP_EXTRACT([Description], "Target Name: (.*?) ")

Alternatively in Tableau 9 you can use the new FINDNTH function:

MID(
     [Description],
     FINDNTH([Description]," ", 5) + 1, 
     FINDNTH([Description]," ", 6) - FINDNTH([Description]," ", 5) - 1
   )

Prior to Tableau 9 you'd have to use string manipulation methods similar to what you've tried, just need to be very careful with arithmetic and providing the right arguments (the third argument in MID is length, not index of the end character, so we need to subtract the index of the start character):

MID(
   [Description]
   , FIND([Description], "Target Name:") + 13
   , FIND([Description], " ", FIND([Description], "Target Name:") + 15)
     - (FIND([Description], "Target Name:") + 13)
)

回答2:

Well, you need to find "Target name: " and then the " " after it, not so hard. I'll split in 3 fields just to be more clear (you can mix everything in a single field). BTW, you were in the right direction, but the last field on MID() should be the string length, not the char position

[start]:

FIND([Description],"Target name: ")+13

[end]:

FIND([Description]," ",[start])

And finally what you need:

MID([Description],[start]+1,[end]-[start]-1)

This should do. If you want to pursue the 5th and 6th " " approach, I would recommend you to find each of the " " until the 6th.

[1st]:

FIND([Description], " ")

[2nd]:

FIND([Description], " ",[1st] + 1)

And so on. Then:

MID([Description],[5th]+1,[6th]-[5th]-1)

回答3:

A simple solution -

SPLIT( [Description], " ", 3 )

This returns a substring from the Description string, using the space delimiter character to divide the string into a sequence of tokens.

The string is interpreted as an alternating sequence of delimiters and tokens. So for the string abc-defgh-i-jkl, where the delimiter character is ‘-‘, the tokens are abc, defgh, i and jlk. Think of these as tokens 1 through 4. SPLIT returns the token corresponding to the token number. When the token number is positive, tokens are counted starting from the left end of the string; when the token number is negative, tokens are counted starting from the right. - Tableau String Functions

回答4:

I don't know Tableau, but perhaps something like this?

MID(
    MID([Description], FIND([Description],"Target Name: ") + 13, 50),
    1,
    FIND(MID([Description], FIND([Description],"Target Name: ") + 13, 50), " ")
)