need to fetch the value between two delimiter

Question

need to fetch the value between two delimiter

vivekkumar341

Say Hey Kid

Points: 661
More actions
August 19, 2012 at 11:24 am

#265479

declare
@text varchar(max) = 'MSH|^~\&|||||21110428134755-0500||ADT^A04^ADT_A01|21110428134755|P|2.5.1|||AL|NE
EVN|A08|21110428133821-0500||||21110428133821-0500
PID|zdly100001|zdly111000^556|PATID1234^5^M11|6g7g7|JONES^WILLIAM^Aryan^III^Dr.^MBA|Singh|19621225000000|M||2106-3|1200 N ELM STREET^^GREENSBORO^NC^27401-1020^TX|GL|(919)379-1212^^^test1@gmail.com^TX^324^5656568^121|(919)271-3434~(919)277-3114||D^|CHR^|PATID12345001^2^M10|66666611111|9-87654^NC^20121217||H|||||||20181220225700
NK1|1||EMR^Employer^HL71113|9900 Spectrum Drive^^Austin^TX^78717^^B||(512)257-5200^WPN^^^^512^2575200|E^Employer^HL71111|21110813||Support Representative||87|e-MDs, Inc.|| '
i want to fetch the value "WILLIAM" FROM @text
which is present In "PID" line after 5th pipe sign "|"
how should i fetch it?

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

vivekkumar341 (8/19/2012)
declare
@text varchar(max) = 'MSH|^~\&|||||21110428134755-0500||ADT^A04^ADT_A01|21110428134755|P|2.5.1|||AL|NE
EVN|A08|21110428133821-0500||||21110428133821-0500
PID|zdly100001|zdly111000^556|PATID1234^5^M11|6g7g7|JONES^WILLIAM^Aryan^III^Dr.^MBA|Singh|19621225000000|M||2106-3|1200 N ELM STREET^^GREENSBORO^NC^27401-1020^TX|GL|(919)379-1212^^^test1@gmail.com^TX^324^5656568^121|(919)271-3434~(919)277-3114||D^|CHR^|PATID12345001^2^M10|66666611111|9-87654^NC^20121217||H|||||||20181220225700
NK1|1||EMR^Employer^HL71113|9900 Spectrum Drive^^Austin^TX^78717^^B||(512)257-5200^WPN^^^^512^2575200|E^Employer^HL71111|21110813||Support Representative||87|e-MDs, Inc.|| '
Suppose above is the string where i want to fetch the value
"WILLIAM"
which is present In "PID" segment line
how should i fetch it?

please read again what you have posted.

could you understand your question?

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

vivekkumar341 Say Hey Kid Points: 661 More actions · Answer 2

Please check now my post and let me know if its clear...

Thanks for your reply

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

Just a guess here but it looks like your data is trying to define a matrix of values with row and column delimiters of | and ^?

In that case, I'd look into using Jeff Moden's DelimitedSplit8K FUNCTION (try a search) on your string.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

If we split your string based on the | deliminator, the result is as follows: -

ItemNumber Item

-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 MSH

2 ^~\&

3 NULL

4 NULL

5 NULL

6 NULL

7 21110428134755-0500

8 NULL

9 ADT^A04^ADT_A01

10 21110428134755

11 P

12 2.5.1

13 NULL

14 NULL

15 AL

16 NE

EVN

17 A08

18 21110428133821-0500

19 NULL

20 NULL

21 NULL

22 21110428133821-0500

PID

23 zdly100001

24 zdly111000^556

25 PATID1234^5^M11

26 6g7g7

27 JONES^WILLIAM^Aryan^III^Dr.^MBA

28 Singh

29 19621225000000

30 M

31 NULL

32 2106-3

33 1200 N ELM STREET^^GREENSBORO^NC^27401-1020^TX

34 GL

35 (919)379-1212^^^test1@gmail.com^TX^324^5656568^121

36 (919)271-3434~(919)277-3114

37 NULL

38 D^

39 CHR^

40 PATID12345001^2^M10

41 66666611111

42 9-87654^NC^20121217

43 NULL

44 H

45 NULL

46 NULL

47 NULL

48 NULL

49 NULL

50 NULL

51 20181220225700

NK1

52 1

53 NULL

54 EMR^Employer^HL71113

55 9900 Spectrum Drive^^Austin^TX^78717^^B

56 NULL

57 (512)257-5200^WPN^^^^512^2575200

58 E^Employer^HL71111

59 21110813

60 NULL

61 Support Representative

62 NULL

63 87

64 e-MDs, Inc.

65 NULL

66 NULL

Are the name details (JONES^WILLIAM^Aryan^III^Dr.^MBA) always in that 27th position? It looks to me like you need to split your original string on |, then split the string at position 27 on ^, is that right?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

SELECT -- resolve "word" between 5th and 6th pipe characters using Cascaded (CROSS) APPLY

WordInRow3 = SUBSTRING(row3,r5.p+1,(r6.p-r5.p)-1)

FROM (SELECT Stringy = @text, LineDelimiter = CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)) d

CROSS APPLY (SELECT p = CHARINDEX(LineDelimiter,Stringy,1)) x1

CROSS APPLY (SELECT p = CHARINDEX(LineDelimiter,Stringy,x1.p+4)) x2

CROSS APPLY (SELECT p = CHARINDEX(LineDelimiter,Stringy,x2.p+4)) x3

CROSS APPLY (SELECT row3 = SUBSTRING(Stringy,x2.p+4,(x3.p-x2.p)-4)) w

CROSS APPLY (SELECT p = CHARINDEX('|',row3,1)) r1

CROSS APPLY (SELECT p = CHARINDEX('|',row3,r1.p+1)) r2

CROSS APPLY (SELECT p = CHARINDEX('|',row3,r2.p+1)) r3

CROSS APPLY (SELECT p = CHARINDEX('|',row3,r3.p+1)) r4

CROSS APPLY (SELECT p = CHARINDEX('|',row3,r4.p+1)) r5

CROSS APPLY (SELECT p = CHARINDEX('|',row3,r5.p+1)) r6

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden