返回字符串中第 5 个和第 6 个空格之间的字符串 [英] Returning the string between the 5th and 6th Spaces in a String

问题描述

我有一列看起来像这样的字符串:

I have a column of strings that look like this:

目标主机:dcmxxxxxxc032.erc.nam.fm.com 目标名称:dxxxxxxgsc047.erc.nam.fm.com 文件系统/u01 有 4.98% 的可用空间- 低于警告 (20) 或严重 (5) 阈值.

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.

列名是[说明]

我想返回的子串是 (dxxxxxxgsc047.erc.nam.fm.com)

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

这个数据唯一的一致性是，想要的字符串出现在字符串中第 5 次和第 6 次出现的空格另一个"，因此我尝试获取第 5 个和第 6 个空格之间的子字符串.

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.

我试过了

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

但这行不通.

(我们使用 Tableau 8.2，只有 Tableau 9 的功能不能成为解决方案的一部分，谢谢！)

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

预先感谢您的帮助.

推荐答案

在 Tableau 9 中，您可以在公式中使用正则表达式，它使任务更简单:

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

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

或者，您可以在 Tableau 9 中使用新的 FINDNTH 函数:

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

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

在 Tableau 9 之前，您必须使用类似于您尝试过的字符串操作方法，只需要非常小心算术并提供正确的参数(MID 中的第三个参数)是长度，不是结束字符的索引，所以我们需要减去开始字符的索引):

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

这篇关于返回字符串中第 5 个和第 6 个空格之间的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！