我需要帮助使用 TSQL 解析 HL7 字符串

Question

我在表格中有一个列，看起来像这样

Name
WALKER^JAMES^K^^
ANDERSON^MICHAEL^R^^
HUFF^CHRIS^^^
WALKER^JAMES^K^^
SWEARINGEN^TOMMY^L^^
SMITH^JOHN^ JACCOB^^

我需要编写一个如下所示的查询

Name	FirstName	LastName	MiddleName
WALKER^JAMES^K^^	JAMES	WALKER	K
ANDERSON^MICHAEL^R^^	MICHAEL	ANDERSON	R
HUFF^CHRIS^^^	CHRIS	HUFF
BUTLER^STEWART^M^^	BUTLER	M	SWEARINGEN
^TOMMY^L^^	TOMMY	SWEARINGEN	L
SMITH^JOHN^JACCOB^^	约翰	STEWART	史密斯JACCOB

I需要帮助生成 LastName 列。

这是我到目前为止所尝试过的

SUBSTRING
(
    --SEARCH THE NAME COLUMN
    Name,
    --Starting after the first '^'
    CHARINDEX('^', Name) + 1 ),
    --Index of second ^ minus the index of the first ^
    (CHARINDEX('^', PatientName, CHARINDEX('^', PatientName) +1)) - (CHARINDEX('^', PatientName))
)

这会产生：

传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

我知道这可行，因为如果我将减号更改为加号，它就会按预期执行。 它产生正确的整数。

我哪里错了？有更好的方法吗？

Answer 1

如果您使用的是最新的 SQL Server 版本 2016 13.x 或更高版本，则可以最大限度地利用 string_split 函数与 ordinal（位置）。

declare @strTable table(sqlstring varchar(max))
insert into @strTable (sqlstring) values ('WALKER^JAMES^K^^')
insert into @strTable (sqlstring) values ('ANDERSON^MICHAEL^R^^')
insert into @strTable (sqlstring) values ('HUFF^CHRIS^^^')
insert into @strTable (sqlstring) values ('SWEARINGEN^TOMMY^L^^');

with tmp as 
    (select value s, Row_Number() over (order by (select 0)) n from @strTable
    cross apply String_Split(sqlstring, '^', 1))

select t2.s as FirstName, t1.s as LastName, t3.s as MiddleInitial from tmp t1
left join tmp t2 on t2.n-t1.n = 1
left join tmp t3 on t3.n-t1.n = 2
where t1.n = 1 or t1.n % 5 = 1
    

Answer 2

我推荐 SUBSTRING()，因为它的性能最佳。 SUBSTRING 的挑战是很难跟踪嵌套的 CHARDINDEX() 调用，因此最好将计算分解为多个部分。我使用 CROSS APPLY 为找到的每个“^”添加别名，并从那里开始搜索下一个。还允许执行 NULLIF() = 0，因此如果找不到“^”，它只会返回 NULL，而不是

使用 SUBSTRING() 和 CROSS APPLY 错误输出 Parse Delimited String

DROP TABLE IF EXISTS #Name

CREATE TABLE #Name (ID INT IDENTITY(1,1) PRIMARY KEY,[Name] varchar(255))
INSERT INTO #Name
VALUES ('WALKER^JAMES^K^^')
,('ANDERSON^MICHAEL^R^^')
,('HUFF^CHRIS^^^')
,('SWEARINGEN^TOMMY^L^^');

SELECT ID
    ,A.[Name]
    ,LastName       = NULLIF(SUBSTRING(A.[Name],0,idx1),'')
    ,FirstName      = NULLIF(SUBSTRING(A.[Name],idx1+1,idx2-idx1-1),'')
    ,MiddleInitial  = NULLIF(SUBSTRING(A.[Name],idx2+1,idx3-idx2-1),'')
FROM #Name AS A
CROSS APPLY (SELECT idx1 = NULLIF(CHARINDEX('^',[Name]),0)) AS B
CROSS APPLY (SELECT idx2 = NULLIF(CHARINDEX('^',[Name],idx1+1),0)) AS C
CROSS APPLY (SELECT idx3 = NULLIF(CHARINDEX('^',[Name],idx2+1),0)) AS D