使用Substring & patindex将字符串的一部分提取到新的列MS SQL中

I have a field in MS SQL that is concatenated like this TopLevel>MidLevel>Lowestlevel tis string varies in length. Sometimes the string is shorter with only a topLevel or a toplevel>MidLevel What I need to do is take the string apart at the ">" and put the text in between into new columns/fields. I tried parsename which worked great until the length started changing.

我在MS SQL中有一个字段，它被连接在一起，就像这个*>中端>中端tis字符串的长度不同。有时字符串更短，只有一个*水平或一个*>中级水平，我需要做的是将字符串在“>”上分开，并将文本放在中间的新列/字段中。我尝试了一种叫parsename的方法，这种方法非常有效，直到长度开始变化。

What I have does not error out but does not produce anything either - except 3 empty columns.

我所拥有的不会出错，但也不会产生任何东西——除了3个空列。

SELECT 
[program_Name]

,substring([Program_Name],1, patindex([Program_Name], '>')) as UP
,substring([Program_Name],patindex([Program_Name], '>*>') - patindex([Program_Name], '>'), patindex([Program_Name], '>*>')) as MID
,substring([Program_Name],patindex([Program_Name], '>*>*>') - patindex([Program_Name], '>*>'), patindex([Program_Name], '>*>')) as LOW


 */
  FROM [dbo].[MSP_EpmProject_UserView]

1 个解决方案

#1

Using PARSENAME (as long as data itself does not containt .):

使用PARSENAME(只要数据本身不包含内容):

CREATE TABLE #tab(col NVARCHAR(100));
INSERT INTO #tab(col) VALUES( 'TopLevel>MidLevel>Lowestlevel');

SELECT 
  PARSENAME(REPLACE(col, '>', '.'),3) AS UP,
  PARSENAME(REPLACE(col, '>', '.'),2) AS MID,
  PARSENAME(REPLACE(col, '>', '.'),1) AS LOW
FROM #tab;

LiveDemo

LiveDemo

Output:

输出:

╔══════════╦══════════╦═════════════╗
║    UP    ║   MID    ║     LOW     ║
╠══════════╬══════════╬═════════════╣
║ TopLevel ║ MidLevel ║ Lowestlevel ║
╚══════════╩══════════╩═════════════╝

Remarks:

备注:

With PARSENAME you could get up to 4 levels
使用PARSENAME你可以达到4级
PARSENAME works with SYSNAME which is alias for NVARCHAR(128)
PARSENAME使用SYSNAME, NVARCHAR(128)的别名
if you have TOP.DATA>MID.DATA>LOW.DATA you could first change . to ^, then > to . and at the end once more ^ to .
如果你有TOP.DATA > MID.DATA >低。您可以首先更改的数据。^,然后>。最后一次^。

LiveDemo2

LiveDemo2

EDIT:

编辑:

Using PARSENAME is a bit hacky so let's do it old fashion way:

使用PARSENAME有点陈腐，所以让我们用旧的方式:

CREATE TABLE #tab(col NVARCHAR(1000));
INSERT INTO #tab(col) VALUES( 'TOP>MID>LOW'), ('A>B>C>D');

WITH tally AS
(
SELECT TOP 5000 rn = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
  FROM master..spt_values
), cte AS
( SELECT col, s.val, pos
  FROM #tab t
  CROSS APPLY(
    SELECT SUBSTRING('>' + t.col + '>', rn + 1,
           CHARINDEX('>', '>' + t.col + '>', rn + 1) - rn -1)AS VAL,
           pos = ROW_NUMBER() OVER (ORDER BY rn)
    FROM tally
    WHERE rn <= LEN('>' + t.col + '>') - 1
      AND SUBSTRING('>' + t.col + '>', rn, 1) = '>') AS s(val, pos)
)
SELECT col, [1] AS UP, [2] AS MID, [3] AS LOW
FROM cte
PIVOT (MAX(val) FOR pos IN ([1],[2],[3],[4],[5])) AS pvt;

Now you can have more levels(just add [4],[5], ... to SELECT column list) and so on.

现在您可以有更多的级别(只需添加[4]，[5]，…)选择列列表)等等。

LiveDemo3

LiveDemo3

#1