sql - 根据第 7 个分隔符拆分 sql 字符串值

Question

当前列值

(下面是临时表的列值，这里的值是动态变化的)

45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00.............. etc

需要根据第 7 个 PIPE 进行划分，即在测试消息之后

输出应该是

字符串 1

45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg

和(作为第二个字符串)

字符串 2

TBL101 | PC | 1.00 | COMP101 | CS | 1.00......... etc

函数

CREATE FUNCTION dbo.SUBSTRING_INDEX
(
   @str NVARCHAR(4000),
   @delim NVARCHAR(1),
   @count INT
)
RETURNS NVARCHAR(4000)
WITH SCHEMABINDING
BEGIN
   DECLARE @XmlSourceString XML;
   SET @XmlSourceString = (SELECT N'<root><row>' + REPLACE( (SELECT @str AS '*' FOR XML PATH('')) , @delim, N'</row><row>' ) + N'</row></root>');
RETURN STUFF
(
    ((
        SELECT  @delim + x.XmlCol.value(N'(text())[1]', N'NVARCHAR(4000)') AS '*'
        FROM    @XmlSourceString.nodes(N'(root/row)[position() <= sql:variable("@count")]') x(XmlCol)
        FOR XML PATH(N''), TYPE
    ).value(N'.', N'NVARCHAR(4000)')), 
    1, 1, N''
);
END

开始

DECLARE @EmpId NVARCHAR(1000)
select @EmpId = temp from OMSOrderTemp


SELECT dbo.SUBSTRING_INDEX(@EmpId, N'|', 7) AS Result;e

Result 中仅显示 string1 且仅显示第一行。

Answer 1

花点时间为你解决问题，我已经用自己的逻辑修改了你的函数，你可以试试这个，这是表值函数，即这个函数将返回表

CREATE FUNCTION dbo.SUBSTRING_INDEX
(
  @str NVARCHAR(4000),
  @delim NVARCHAR(1),
  @count INT
)RETURNS @rtnTable TABLE 
(
   FirstString  NVARCHAR(2000),
   SecondString NVARCHAR(2000)
)
AS
BEGIN
    DECLARE @cnt INT=1;
    DECLARE @subStringPoint INT = 0
    WHILE @cnt <=@count
    BEGIN 
            SET @subStringPoint=CHARINDEX(@delim,@str,@subStringPoint)+1
            SET @cnt=@cnt+1
    END

    INSERT INTO @rtnTable
    SELECT SUBSTRING(@str,0,@subStringPoint-1) ,SUBSTRING(@str,@subStringPoint+1,LEN(@str)) 
    RETURN
END 

调用这个函数

DECLARE @s varchar(MAX)='45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg | TBL101 | PC | 1.00 | COMP101 | CS | 1.00'
SELECT * FROM dbo.SUBSTRING_INDEX (@s,'|',7)

这将给出两列输出

45 | 00055 | 9/30/2016 | Vodafone | Randy Singh | Newyork | Test Msg    TBL101 | PC | 1.00 | COMP101 | CS | 1.00