如何仅使用MS SQL语法解析HL7定界符 [英] How to parse HL7 delimiters using only MS SQL Syntax
问题描述
共享仅使用MS SQL语法分析原始HL7数据的代码.这是拆分所有|的快速方法.和^分隔符，并将每个数据存储为一行. 我想分享这一点，因为我一直在寻找严格通过SQL语法解析HL7消息的方法，并且找不到任何资源.
Sharing this code that parses raw HL7 data using only MS SQL Syntax. It's a quick way of splitting all the | and ^ delimiters and store each data as a row. I wanted to share this because I've been looking around for way to parse HL7 Message strictly through SQL Syntax and was not able to find any resources.
我要指出的是，这不是将HL7消息导入SQL数据库的最有效方法.还有其他更好的方法(例如:C#应用程序-HL7> XML> DB，第三方软件，大容量插入等).
I would put it out there that this is not the most efficient method to import HL7 messages to SQL databases. There are other better methods (ie: C# app - HL7 > XML > DB, 3rd party software, BULK INSERT etc.).
这是SQL语法，将读取|和^定界符，并将它们存储在TempSplit和TempSplit2表下的行中.这里的假设是您有一种方法可以将原始HL7文件导入数据库(即:SSIS).从那时起，您可以将TempSplit2下存储的结果映射到SQL Segment表.
This is SQL syntax that will read through the | and ^ delimiter and store them in rows under TempSplit and TempSplit2 tables. The assumption here is that you have a way to import the raw HL7 file into a database (ie: SSIS). From then on, you can map the results stored under TempSplit2 to your SQL Segment table.
ID列= |
ID2列= ^之间的每个值的位置
ID2 column = position of each value between ^
作为第一个测试，您可以将HL7文件raw(原样)导入2列.参见下面的示例:
As a first test you can import the HL7 file raw(as is) into 2 column. See sample below:
Row val Filename
------------------------------------------------------------------
1 MSH|^~\&|EIH7| HL7_Filename.dat
AB-PBA^AB PBA^ISO|Company|TestComp|
20160830230713||ADT^A04|23071
408302016752373|P|2.6|
如下面scsimon所述，光标循环两次并解析所有数据，而不是您所需的数据.在我当前的项目中，这很重要，但也许对您而言并不重要.随时修改
As mentioned by scsimon below, the cursor loops twice and parses all data, rather than what you just need. In my current project, this was important but maybe not on yours. Feel free to modify it
代码如下:
USE <yourdb>
Go
set nocount on
Create table dbo.TempSplit(
filename varchar(MAX),
RecordType varchar(MAX),
value varchar(MAX) ,
id int )
Create table dbo.TempSplit2(
filename varchar(MAX),
RecordType varchar(MAX),
value varchar(MAX) ,
id int ,
subvalue varchar(MAX) ,
id2 int )
truncate table dbo.TempSplit
truncate table dbo.TempSplit2
DECLARE HL7_Cursor CURSOR FOR select distinct filename
from <your raw HL7 table>
DECLARE @tempid varchar(max)
Open HL7_Cursor
Fetch next from hl7_cursor into @tempid
While @@FETCH_STATUS = 0
BEGIN
Declare @Rowcnt int=0
DECLARE HL7_Cursor2 CURSOR FOR select val from <<your raw HL7 table>> where filename = @tempid
DECLARE @tempid2 varchar(max)
Open HL7_Cursor2
Fetch next from hl7_cursor2 into @tempid2
While @@FETCH_STATUS = 0
BEGIN
set @Rowcnt = @Rowcnt +1
DECLARE @RtnValue table
(
value varchar(MAX) ,
id int
)
DECLARE @result varchar(1000),
@List varchar(MAX),
@SplitOn varchar(5),
@GetIndex smallint,
@ID int,
@val varchar(max),
@recordid varchar(max),
@filename varchar(1000) ,
@cnt int=0
SET @list = @tempid2
SET @SplitOn = '|'
SET @GetIndex = 0
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@SplitOn, @list)
WHILE @start < LEN(@list) + 1 BEGIN
BEGIN
IF @end = 0
begin
SET @end = LEN(@list) + 1
end
END
Insert Into @RtnValue (id,value)
Select @cnt,SUBSTRING(@list, @start, @end - @start)
SET @start = @end + 1
SET @end = CHARINDEX(@SplitOn, @list, @start)
set @cnt= @cnt+1
END
insert into dbo.TempSplit
select @tempid filename,
case when
(Select count(value)
from @RtnValue) = 0 then value
else
(Select value
from @RtnValue
where id =0)end Recordtype,
value,id,@Rowcnt from @RtnValue
delete from @RtnValue
set @cnt =0
FETCH NEXT From HL7_Cursor2 into @tempid2
END
Close hl7_cursor2
deallocate hl7_cursor2
FETCH NEXT From HL7_Cursor into @tempid
END
Close hl7_cursor
deallocate hl7_cursor
truncate table dbo.TempSplit2
DECLARE HL7_Cursor3 CURSOR FOR select * from dbo.TempSplit
DECLARE @file varchar(max),@rt varchar(max),@valu varchar(max),@idz int
,@rowcnt2 int
Open HL7_Cursor3
Fetch next from hl7_cursor3 into @file,@rt,@valu,@idz,@rowcnt2
While @@FETCH_STATUS = 0
BEGIN
DECLARE @RtnValue2 table
(
value varchar(MAX) ,
id int,
filename varchar(MAX)
)
DECLARE @result2 varchar(1000),
@List2 varchar(MAX),
@SplitOn2 varchar(5),
@GetIndex2 smallint,
@ID2 int,
@val2 varchar(max),
@recordid2 varchar(max),
@filename2 varchar(1000) ,
@cnt2 int=0
SET @list2 = @valu
SET @SplitOn2 = '^'
SET @GetIndex2 = 0
DECLARE @start2 INT, @end2 INT
SELECT @start2 = 1, @end2 = CHARINDEX(@SplitOn2, @list2)
WHILE @start2 < LEN(@list2) + 1 BEGIN
BEGIN
IF @end2 = 0
begin
SET @end2 = LEN(@list2) + 1
end
END
Insert Into @RtnValue2 (id,value,filename)
Select @cnt2,SUBSTRING(@list2, @start2, @end2 - @start2) ,@file
SET @start2 = @end2 + 1
SET @end2 = CHARINDEX(@SplitOn2, @list2, @start2)
set @cnt2= @cnt2+1
END
insert into dbo.TempSplit2
select @file,@rt,@valu,@idz,value,id,@rowcnt2 from @RtnValue2
delete from @RtnValue2
set @cnt2 =0
FETCH NEXT From HL7_Cursor3 into @file,@rt,@valu,@idz ,@rowcnt2
End
Close hl7_cursor3
deallocate hl7_cursor3
推荐答案
帖子中的代码应通过|的HL7消息定界符进行解析.和^，如果要通过〜分隔符进行解析，则需要为其添加另一个游标.
The code on the post should parse through the HL7 Message delimiters of | and ^, if you want to parse through the ~ delimiter, then you will need to add another cursor for it.
这是它看起来像的最终结果:
Here's the end result of what it should look like:
Row filename RecordType Value id Subvalue id2 RecordTypeID
------------------------------------------------------------------
1 HL7_filename.dat MSH MSH 0 MSH 0 1
2 HL7_filename.dat MSH ^~\& 1 0 1
3 HL7_filename.dat MSH ^~\& 1 ^~\& 1 1
4 HL7_filename.dat MSH EIH7 2 EIH7 0 1
从这里开始，您可以将基于"RecordType"，"id"和"id2"的子值数据映射到您的相应表.如果在一个文件中多次发送同一段，则使用RecordTypeID计数器.
From here on, you can map the subvalue data based on the "RecordType", "id" and "id2" to your corresponding tables. RecordTypeID counter is used if the same segment was sent more than once in one file.
希望这对那些受困的人有帮助
Hopefully this will be a help to some who are stuck
这篇关于如何仅使用MS SQL语法解析HL7定界符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
