I need help parsing an HL7 string with TSQL

Question

I have a column in a table that looks like this

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

I need to write a query that looks like this

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

I need help generating the LastName column.

This is what I've tried so far

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

This produces:

Invalid length parameter passed to the LEFT or SUBSTRING function.

I know this can work because if I change the minus sign to a plus sign it performs as expected. It produces the right integer.

Where am I going wrong? Is there a better way to do this?

Answer 1

I recommend SUBSTRING() as it will perform the best. The challenge with SUBSTRING is it's hard to account to keep track of the nested CHARDINDEX() calls so it's better to break the calculation into pieces. I use CROSS APPLY to alias each "^" found and start from there to search for the next. Also allows to do NULLIF() = 0, so if it can't find the "^", it just returns a NULL instead of erroring out

Parse Delimited String using SUBSTRING() and CROSS APPLY

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

Answer 2

If you are using the latest SQL Server versions 2016 13.x or higher, you can maximize the use of string_split function with ordinal (position).

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