Selecting data after certain characters

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/

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/

babloo
Starting Member

35 Posts

Posted - 2014-05-07 : 14:01:41

Thanks tkizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources