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?

score 1 · Answer 1 · edited Mar 16 '22 at 14:35

1

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

edited Mar 16 '22 at 14:35

Mark Rotteveel

100,966
191
140
197

answered Mar 16 '22 at 03:26

Ed Bangga

12,879
4
16
30

Is there a way to do this if there are 10000+ rows? What can you substitute the names for? – mccollough Mar 16 '22 at 05:34
enable_ordinal parameter for ```STRING_SPLIT()``` is actually only available for Azure as of today. Also doing it this way I believe will not be nearly as efficient as just using SUBSTRING – Stephan Mar 16 '22 at 14:32

score 0 · Answer 2 · answered Mar 16 '22 at 14:33

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

This should scale well enough to handle millions of rows if need be. Nothing in the query would need to change for more rows of data, just point the query at your table — Stephan, Mar 17 '22 at 03:59

I need help parsing an HL7 string with TSQL

2 Answers2

Parse Delimited String using SUBSTRING() and CROSS APPLY