Returning the string between the 5th and 6th Spaces in a String

Question

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.

Hanford

It's so hard to find such well structured questions on Tableau thread, that I must congratulate you! May all questions be as good as yours! – Bubble

Haha thanks, if someone is going to take the time to help me out, I try to make it as painless as possible for them. – Hanford

This is simple if your version of Tableau has the Split function (I don't remember when that arrived) as this will split a string on a defined character and all you to extract the nth substring in the result. – Curler

Answer 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)
)

Plafond

I don't think FINDNTH() was available until Tableau 9 either. So that part of the answer is not completely right. – Neukam

If your DB supports REGEX then Max's answer is the best way to go. – Chaplain

Yeah that's correct, we use 8.2 here, I did see that was coming in 9. Any ideas on how to do it without the functions only available in 9? – Hanford

Didn't know that FINDTH function, pretty handy. Thanks! – Bubble

@AlexBlakemore @Hanford -- thanks for correcting me, FINDNTH is indeed a new function in 9.0 as well as REGEXP_EXTRACT. I've updated the answer with a formula that works in 8.2. Hope that helps. – Plafond

@Hanford you mean some input rows have different format? in some cases it might really be easier to edit those rows in the data source... :) so that they all have the same format... – Plafond

No I was screwing it up with something I was adding to the front of it, your code works perfectly. One part I don't quite understand, the last section FIND([Description], "Target Name:") + 15) - (FIND([Description], "Target Name:") + 13) Wouldn't that return 2 every time? – Hanford

@Hanford no, if you read carefully, there are nested FIND functions... basically I'm trying to FIND the first space following the space after "Target Name"... if that makes sense – Plafond

Answer 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)

Bubble

Answer 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

Nathalie

Answer 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), " ")
)

Organza

The lengths of the strings I want aren't static, they go anywhere from 8 to 40 characters. I need to grab everything between the spaces. Thanks though. – Hanford

Just change the 50 to be wide enough to ensure you grab enough the next space following "Target Name: ". But 50 is already big enough if the range is from 8 to 40. Why won't that work? – Organza

@Max, just because I'm curious. What's wrong with the idea I posted? – Organza

that probably would work, ill check it out when after it's 100% working I start trying to make it more effecient – Hanford