convert string in a line feed with repeat characters into array

Question

convert string in a line feed with repeat characters into array

Frances L

SSCarpal Tunnel

Points: 4727
More actions
September 29, 2010 at 6:45 am

#238923

how to get this line into arrays;
ZB5|BONEBONE|SDES|20081121|1536|||ZB5|NONE|SREQ|20081121|1536|||
ZB5|NOWBC'SSEEN|GS|20081121|1536|||ZB5|NOORGANISMSSEEN|GS|20081121|1536|||ZB5|
RARE~METHICILLINRESISTANTSTAPHAUREUS|CULT|20081121|1536|||ZB5|
GROUPBBETA-HEMOLYTICSTREPTOCOCCI~RECOVEREDFROMSUBCULTUREONLY|
CULT|20081121|1536|||ZB5||RPT|20081121|1536|||MSH|^~\&|||ATLAS||200811241421||ZPU|
ZB5|BONEBONE|SDES|20081121|1536|||ZB5|NONE|SREQ|20081121|1536|||
ZB5|NOWBC'SSEEN|GS|20081121|1536|||ZB5|NOORGANISMSSEEN|GS|20081121|1536|||
ZB5|RARE~METHICILLINRESISTANTSTAPHAUREUS|CULT|20081121|1536|||
ZB5|GROUPBBETA-HEMOLYTICSTREPTOCOCCI~RECOVEREDFROMSUBCULTUREONLY|
CULT|20081121|1536|||ZB5||RPT|20081121|1536|||MSH|^~\&|||ATLAS||200811241421||ZPU|
ZB5|BONEBONE|SDES|20081121|1536|||ZB5|NONE|SREQ|20081121|1536|||
ZB5|NOWBC'SSEEN|GS|20081121|1536|||ZB5|NOORGANISMSSEEN|GS|20081121|1536|||ZB5|
RARE~METHICILLINRESISTANTSTAPHAUREUS|CULT|20081121|1536|||
ZB5|GROUPBBETA-HEMOLYTICSTREPTOCOCCI~RECOVEREDFROMSUBCULTUREONLY|
CULT|20081121|1536|||ZB5||RPT|20081121|1536|||MSH|^~\&|||ATLAS||200811241421||ZPU|
like this
The repeat characters is ZB5.
ZB5|BONEBONE|SDES|20081121|1536|||
ZB5|NONE|SREQ|20081121|1536|||
ZB5|NOWBC'SSEEN|GS|20081121|1536|||
ZB5|NOORGANISMSSEEN|GS|20081121|1536|||
ZB5|RARE~METHICILLINRESISTANTSTAPHAUREUS|CULT|20081121|1536|||
ZB5|GROUPBBETA-HEMOLYTICSTREPTOCOCCI~RECOVEREDFROMSUBCULTUREONLY|CULT|20081121|1536|||
ZB5||RPT|20081121|1536|||MSH|^~\&|||ATLAS||200811241421||ZPU|
Editor's Note: Added line breaks and removed some data as this was causing formatting issues.

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

I think I would try replacing "ZB5|" with char(7) + "ZB5|". Then utilize the delimited split function to split this, using char(7) as the delimiter. (Feel free to use any other desired character as the delimiter, just be sure you change it in both the replace statement and the call to the function.)

Click here for the latest Delimited Split Function.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

My database is in MS ACCESS.

There are no such function exist.

ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,

SUBSTRING(@pString, N, CHARINDEX

WayneS SSC Guru Points: 95460 More actions · Answer 3

Whoops, I thought this was a SQL Server question. Sorry about that.

I don't know Access enough to give a suggestion on how to do it from there.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 4

I did that vb.net with the split function. It is easy. right now I do not want to install application. Thx.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 5

Francis,

Your sample has 8 lines of text with line-breaks.

There are 51 occurrences of "ZB5|"

Do you want the output to be 51 lines?

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 6

I do not know how many lines I will have .

I googled and found this function. It solve my problem. thx.

Public Function PassbackAnyword(pstrText As String, pintword As Integer, pstrdivider As String) As Variant

'Purpose: Given a string, a word and the divider,

' returns the specific word.

'Input: ? PassbackAnyword("1234*7890*8888", 2, "*")

'Output: 7890

Dim intLoop As Integer

Dim intPos As Integer

Dim intprev As Integer

Dim varstring As Variant

'Don't waste your time if the divider isn't in the string

If InStr(pstrText, pstrdivider) <> 0 Then

intPos = 1

intprev = 1

pstrText = pstrText & pstrdivider

For intLoop = 1 To pintword

intPos = InStr(intprev + 1, pstrText, pstrdivider)

If intPos <> 0 Then

If intLoop < pintword Then

intprev = intPos

End If

Else

intPos = intprev

End If

If pintword > 1 And varstring <> "" Then

varstring = Right(varstring, Len(varstring) - 1)

End If

Else

'If it's the first word we want then it's all the string otherwise is nothing

If pintword = 1 Then

varstring = pstrText

End If

If varstring = "" Or varstring = pstrdivider Then

varstring = Null

End If

PassbackAnyword = varstring

End Function

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 7

How about this? Use Acces's Split function using "ZB5|" as the delimiter, then add the "ZB5|" back to the start of each array member

Public Sub SplitLine()

Dim textline() As String

Dim n As Integer

textline() = Split("ZB5|BONEBONE|SDES|20081121|1536|||ZB5|NONE|SREQ|20081121|1536|||ZB5|NOWBC'SSEEN|GS|20081121|1536|||ZB5|NOORGANISMSSEEN|GS|20081121|1536|||ZB5|RARE~METHICILLINRESISTANTSTAPHAUREUS|CULT|20081121|1536|||ZB5|GROUPBBETA-HEMOLYTICSTREPTOCOCCI~RECOVEREDFROMSUBCULTUREONLY|CULT|20081121|1536|||ZB5||RPT|20081121|1536|||MSH|^~\&|||ATLAS||200811241421||ZPU|", "ZB5!")

For n = LBound(textline, 1) To UBound(textline, 1)

texline(n) = "ZB5|" & textline(n)

Next

End Sub