Find nth position in a pipe delimited string

Question

Find nth position in a pipe delimited string

NineIron

SSChampion

Points: 12529
More actions
December 19, 2017 at 7:46 am

#342065

Here's my string, I need to determine if there is a value between the 30th and 31st pipe. Any thoughts?
{MED|10|MG|RC|ONCE|PRN|||||||||||||||||||||||||CONSTIPA|Constipation||||||||||1||Med|BISR|||||||||||}

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

NineIron - Tuesday, December 19, 2017 7:46 AM
Here's my string, I need to determine if there is a value between the 30th and 31st pipe. Any thoughts?
{MED|10|MG|RC|ONCE|PRN|||||||||||||||||||||||||CONSTIPA|Constipation||||||||||1||Med|BISR|||||||||||}

Sure. Take a look at the link in my signature about splitting strings. The splitter you will there from Jeff Moden is about the only one that includes the position number of the parsed values. It is super easy to use and for something like this is painless.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Michael L John One Orange Chip Points: 26551 More actions · Answer 2

NineIron - Tuesday, December 19, 2017 7:46 AM
Here's my string, I need to determine if there is a value between the 30th and 31st pipe. Any thoughts?
{MED|10|MG|RC|ONCE|PRN|||||||||||||||||||||||||CONSTIPA|Constipation||||||||||1||Med|BISR|||||||||||}

SELECT *
FROM DelimitedSplit8K('YourString', '|')
WHERE ItemNumber = 30

The code is here:

http://qa.sqlservercentral.com/articles/Tally+Table/72993/

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

NineIron SSChampion Points: 12529 More actions · Answer 3

NineIron

SSChampion

Points: 12529

December 19, 2017 at 8:08 am

#1972696

Wonderful.Thanx.

NineIron SSChampion Points: 12529 More actions · Answer 4

Pardon my ignorance but, how would I apply the function to this query in order to return records that have a value in position 31 of the field, OOSOR.OrderRecordValue?
Also, How do I format the code to eliminate the spacing?

selectOOSOR.SourceID,

OOSOR.OrderRecordUrnID,

OOSOR.OrderRecordValue,

OOSLTOR.ProcedureUrnID

from livefdb.dbo.OmOrdSet_OrderRecords OOSOR

inner join (selectSourceID,

OmOrdSetID,

ProcedureUrnID,

substring(LinkToOrderRecordsRidID, charindex('|',LinkToOrderRecordsRidID)+1,18) as OrderRecordUrnID

from livefdb.dbo.OmOrdSet_LinkToOrderRecords

where SourceID='BRO'

) OOSLTOR

on OOSOR.SourceID=OOSLTOR.SourceID

and OOSOR.OmOrdSetID=OOSLTOR.OmOrdSetID

and OOSOR.OrderRecordUrnID=OOSLTOR.OrderRecordUrnID

where OOSOR.SourceID='BRO'

and OOSOR.OrderRecordID='PhaData'

and OOSOR.OmOrdSetID='Z.ADMMS1'

create table #T

(

OrderRecordUrnID varchar(50),

OrderRecordValue varchar(500),

ProcedureUrnID varchar(5)

)

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130317','{MED|10|MG|IV|AC|SCH||||||||||||||||||||||||||||||||||||1||Med|}','262')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130349','{MED|10|MG|IV|Q8H|SCH||||||||||||||||||||||||||||||||||||3||Med|}','262')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130380','{MED|17|GM|PO|DAILY|PRN|||||||||||||||||||||||||CONST|Constipation||||||||||2||Med|||||||||||||||||||||||}','263')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130411','{MED|8|MG|PO|QHS|SCH||||||||||||||||||||||||||||||||||||1||Med||||||||||||}','264')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130442','{MED|8|MG|PO|QHS|PRN|||||||||||||||||||||||||SLEEP|Sleep||||||||||2||Med|||||||||||||||||||||||}','264')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201710171632550212','{MED|2|MG|IV|Q4H|PRN|||||||||||||||||||||||||MOD|Moderate Pain||||||||||2||Med|||||||||||||||||||||||}','267')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201402031500100782','{MED|1|EACH|PO|Q4H|PRN|||||||||||||||||||||||||MOD|Moderate Pain||||||||||||Med|T3||||||||||||||||||||||}','10')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201202031428540964','{MED|400|MG|PO|Q6H|PRN|||||||||||||||||||||||||MILD PAIN|Mild Pain||||||||||1||Med|IBU400||||||||||||||||||||||}','11')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201202031428550027','{MED|5|MG|PO|Q6H|PRN|||||||||||||||||||||||||SP|Severe Pain||||||||||1||Med|OXYCO5||||||||||||||||||||||}','17')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201202031428550043','{MED|20|MG|PO|BID|SCH||||||||||||||||||||||||||||||||||||1||Med|FAMO20}','27')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201202031428540808','{MED|10|MG|RC|ONCE|PRN|||||||||||||||||||||||||CONSTIPA|Constipation||||||||||1||Med|BISR|||||||||||}','32')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201402031500100766','{MED|1|EACH|PO|Q4H|PRN|||||||||||||||||||||||||SP|Severe Pain||||||||||||Med|PCET||||||||||||||||||||||}','72')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201204031246470671','{MED|100|MG|PO|BID|SCH||||||||||||||||||||||||||||||||||||1||Med|DSS|||||||||||}','125')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201204051332400321','{MED|40|MG|PO|DAILY|SCH||||||||||||||||||||||||||||||||||||2||Med|PAN40|||||||||||}','128')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201204051332400337','{MED|40|MG|IV|DAILY|SCH||||||||||||||||||||||||||||||||||||1||Med|PAN40P|||||||||||}','129')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201312171018280907','{MED|650|MG|PO|Q6H|PRN||||||||||||||||||||||||||Headache or fever > 101||||||||||1||Med|APAP||||||||||||||||||||||}','166')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191405170551','{MED|0.6|MG|IV|Q4H|PRN||||||||||||||||||||||||||Moderate - Severe Pain||||||||||1||Med|||||||||||||||||||||||}','178')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191405170552','{MED|0.6|MG|SC|Q4H|PRN|||||||||||||||||||||||||PAIN|Pain||||||||||1||Med|||||||||||||||||||||||}','179')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191405170556','{MED|30|MG|IV|Q6H|PRN|||||||||||||||||||||||||MOD|Moderate Pain||||||||||1||Med|||||||||||||||||||||||}','180')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191405170557','{MED|4|MG|IV|Q6H|PRN|||||||||||||||||||||||||NV|Nausea/Vomiting||||||||||1||Med|||||||||||||||||||||||}','185')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191405170558','{MED|4|MG|IM|Q8H|PRN||||||||||||||||||||||||||||||||||||1||Med|||||||||||||||||||||||}','186')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191405170559','{MED|4|MG|PO|Q6H|PRN|||||||||||||||||||||||||NV|Nausea/Vomiting||||||||||1||Med|||||||||||||||||||||||}','187')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191443200923','{MED|20|MG|IV|Q12H|SCH||||||||||||||||||||||||||||||||||||1||Med||||||||||||}','189')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191443200921','{MED|5|MG|PO|DAILY|PRN|||||||||||||||||||||||||CONST|Constipation||||||||||1||Med|||||||||||||||||||||||}','190')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580775','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||2||IV|||||||||||||Additive|}','193')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580776','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||1||IV|||||||||||||Additive|}','193')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580777','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||3||IV|||||||||||||Additive|}','193')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580778','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||1||IV|||||||||||||Additive|}','194')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580779','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||4||IV|||||||||||||Additive|}','194')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580780','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||3||IV|||||||||||||Additive|}','194')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580781','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||2||IV|||||||||||||Additive|}','194')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580782','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||1||IV|||||||||||||Additive|}','195')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580783','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||3||IV|||||||||||||Additive|}','195')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580784','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||2||IV|||||||||||||Additive|}','195')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580785','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||4||IV|||||||||||||Additive|}','195')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201505191511580786','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||||IV|||||||||||||Additive|}','195')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201601070804410281','{IV|||IV||SCH|||||||||||||||||||||||||||LV|DUR||||||||1||IV|||||||||||||Additive|}','246')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201604271225060823','{MED|2|EACH|PO|QHS|SCH||||||||||||||||||||||||||||||||||||1||Med|}','249')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130333','{MED|10|MG|IV|AC|SCH||||||||||||||||||||||||||||||||||||1||Med|}','262')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130364','{MED|10|MG|IV|Q8H|SCH||||||||||||||||||||||||||||||||||||3||Med|}','262')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130395','{MED|17|GM|PO|DAILY|PRN|||||||||||||||||||||||||CONST|Constipation||||||||||2||Med|||||||||||||||||||||||}','263')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130427','{MED|8|MG|PO|QHS|SCH||||||||||||||||||||||||||||||||||||1||Med||||||||||||}','264')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201709181531130458','{MED|8|MG|PO|QHS|PRN|||||||||||||||||||||||||SLEEP|Sleep||||||||||2||Med|||||||||||||||||||||||}','264')

insert into #T(OrderRecordUrnID, OrderRecordValue, ProcedureUrnID) values('201710171632550228','{MED|2|MG|IV|Q4H|PRN|||||||||||||||||||||||||MOD|Moderate Pain||||||||||2||Med|||||||||||||||||||||||}','267')

Sean Lange SSC Guru Points: 286562 More actions · Answer 5

I am a little confused. You posted a big query for a bunch of tables we don't have. Then you posted ddl and sample for a temp table that is not in the query. I get the idea you are not quite sure how to use the DelimitedSplit8K function in your query but I don't understand what part(s) you need help or what the last post you sent is providing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

SELECT #t.OrderRecordUrnID, s.itemnumber, s.item FROM #t CROSS APPLY dbo.DelimitedSplit8K(#t.OrderRecordValue, '|') s WHERE itemNumber = 31 AND item <> '';

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

NineIron SSChampion Points: 12529 More actions · Answer 7

Sean, Sorry for the confusion but, I really didn't know how to explain myself.
J, Thanx. I get it now. Looks like you understand confusion!

-scott-