Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Selecting data after certain characters

Author  Topic 

babloo
Starting Member

35 Posts

Posted - 2014-05-07 : 10:06:17
Hi - I have a NTEXT field that stores HL7 data for ex:.

MSH|^~\&|WORD|164|DADD|TEST|201404240936||ORU^R01||P|2.3 PID|..alot of HL7 then..OBR|||Specimen #PTA01-1|S^TEST|||

I am trying to pull two items from the example above pull the word or value in the fifth position of '|' after MSH and pull the word or value in the fourth position of '|' after OBR.

So I want something like this:

TEST, S^TEST

I tried Substring but that doesn't help much.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-07 : 13:06:36
You need a splitter function: http://stackoverflow.com/questions/14911167/split-function-in-sql-server-2008.

Use | for the delimiter. Then use ROW_NUMBER() function to get the value you are interested in.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-07 : 13:07:56
@s is your string. For whatever reason, I can't add a declare in my post right now.

select Item from (select ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as RowNumber, Item from dbo.Split(@s, '|')) t
where RowNumber = 6

select Item from (select ROW_NUMBER() OVER(ORDER BY (SELECT 1)) as RowNumber, Item from dbo.Split(substring(@s, charindex('obr', @s), datalength(@s)), '|')) t
where RowNumber = 5

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

babloo
Starting Member

35 Posts

Posted - 2014-05-07 : 14:01:41
Thanks tkizer
Go to Top of Page
   

- Advertisement -