drtopserv
asked on
splitting string field into fields access query OR vba
Hi,
I have a field with this string:
:20:111111251540002D
:23B:CREASDD
:32A:09A1237CHF41912,05
:33B:CHASD1912,05
:50K:/CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
:53A:UBSWCHZH80A
:57D:SFS SDFSDFS
RASDASDA
BRANCH 000
GOGOGOG
:59:/43434
DFSDFL GRSDFOUP
SDFDF + DF LTFDD
DFD HDFDN 343423
GOGOER
:71A:OER
how can i split this string into new field in such a query by this rule:
bring me all the string after ":20:" Until the next ":", etc
result :
field_:20: =111111251540002D
field_:23B:=CREASDD
field_:32A:=09A1237CHF4191 2,05
ETC.....
I have a field with this string:
:20:111111251540002D
:23B:CREASDD
:32A:09A1237CHF41912,05
:33B:CHASD1912,05
:50K:/CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
:53A:UBSWCHZH80A
:57D:SFS SDFSDFS
RASDASDA
BRANCH 000
GOGOGOG
:59:/43434
DFSDFL GRSDFOUP
SDFDF + DF LTFDD
DFD HDFDN 343423
GOGOER
:71A:OER
how can i split this string into new field in such a query by this rule:
bring me all the string after ":20:" Until the next ":", etc
result :
field_:20: =111111251540002D
field_:23B:=CREASDD
field_:32A:=09A1237CHF4191
ETC.....
Select MyField From Table1 Where Left(MyField, 1)=":"
ASKER
Sorry,seems not understand my Q.
What i`m trying to tell, that this exact long string in the field needed to be devided to to many strings and put them in other fields (mentioned in the result . look at the result that i need when executing the solution.
What i`m trying to tell, that this exact long string in the field needed to be devided to to many strings and put them in other fields (mentioned in the result . look at the result that i need when executing the solution.
Please provide some more examples of the field content and the table structure that you want to split it into, as it is not completely clear from your example and we don't want to waste time providing a solution that you don't want.
e.g.
Do you want to split the field at every newline (apparently not)
Do you want to split the field at every [newline followed by colon then alphanumeric string then another colon] (yes?)
please clarify: do all examples of this data have the same "field identifiers" (20,23B,32A,33B,50K...)
should the 50k field include everything up to 53A:
e.g.
Do you want to split the field at every newline (apparently not)
Do you want to split the field at every [newline followed by colon then alphanumeric string then another colon] (yes?)
please clarify: do all examples of this data have the same "field identifiers" (20,23B,32A,33B,50K...)
should the 50k field include everything up to 53A:
I don't know how something this complex could be done in SQL, but it seems a pretty strait-forward solution to simply do some VB code in a loop with the InStr() function and the optional 'start' parameter. Something like this:
Str = YourBigString
'Find the Start of the 1st Field
P1 = InStr( Str, ":")
Do While P1 > 0
'Find End of the Next Field
P2 = InStr(P1+1, Str, ":")
If P2 = 0 Then
Exit Do 'String not formated as expected
End If
FieldName = "Field_" & Mid$( Str, P1, P2 - P1 + 1 )
'Find Data
P1 = P2 + 1
P2 = InStr( P1, Str, ":" )
If P2 > 0 Then
DataStr = Mid$( Str, P1, P2 - P1 ) 'No +1 because the : found starts the next field name
Else
DataStr = Mid$( Str, P1 ) 'Extracts to end of string
End If
'P2 is now the start of the next field name
P1 = P2 'If no final : found, the while loop will end
Fields(FieldName).Value = DataStr
Loop
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Slight tweak to what Jamie suggested to give you what you want:
Function SplitString (myString As String,intToken as Integer) As String
Dim myArray() as Variant
myArray = Split (myString,":")
SplitString = myArray(intToken)
End Function
Your call would be the string/field with the entire date, and the nth token you want returned from it.
Jim.
Function SplitString (myString As String,intToken as Integer) As String
Dim myArray() as Variant
myArray = Split (myString,":")
SplitString = myArray(intToken)
End Function
Your call would be the string/field with the entire date, and the nth token you want returned from it.
Jim.
you can simply use a query, f1 is the field that contains the string
select f1, left(f1,instrrev(f1,":")) as result1, mid(f1,instrrev(f1,":")+1) as result2
from yourTable
where instr(f1,":") >0
with this info
:20:111111251540002D
the result will be
f1 result1 result2
:20:111111251540002D :20: 111111251540002D
i already posted this yesterday and the thread disappeared????
select f1, left(f1,instrrev(f1,":")) as result1, mid(f1,instrrev(f1,":")+1)
from yourTable
where instr(f1,":") >0
with this info
:20:111111251540002D
the result will be
f1 result1 result2
:20:111111251540002D :20: 111111251540002D
i already posted this yesterday and the thread disappeared????
ASKER
kewl Rey,
but i need to keep going and extract others :Anystring:
means:
f1 result1 result2
:20:111111251540002D :20: 111111251540002D
f1 result1 result2
:23B:CREASDD :23B: CREASDD
f1 result1 result2
:50K:/CH110023412400243526 :50K: /CH110023412400243526
MSFSDFER KFSFUTDFR MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2 BUDFSFERSTR. 2
8113 BOSDFSFSLSEN 8113 BOSDFSFSLSEN
Etc.....
but i need to keep going and extract others :Anystring:
means:
f1 result1 result2
:20:111111251540002D :20: 111111251540002D
f1 result1 result2
:23B:CREASDD :23B: CREASDD
f1 result1 result2
:50K:/CH110023412400243526
MSFSDFER KFSFUTDFR MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2 BUDFSFERSTR. 2
8113 BOSDFSFSLSEN 8113 BOSDFSFSLSEN
Etc.....
ASKER
to clarify this :
the field i mention in my question has the long string :
:20:111111251540002D
:23B:CREASDD
:32A:09A1237CHF41912,05
:33B:CHASD1912,05
:50K:/CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
:53A:UBSWCHZH80A
:57D:SFS SDFSDFS
RASDASDA
BRANCH 000
GOGOGOG
:59:/43434
DFSDFL GRSDFOUP
SDFDF + DF LTFDD
DFD HDFDN 343423
GOGOER
:71A:OER
that i would like to split into many new fields,and the delimiter should me -> :numeronly OR numer&Char:
as you can see : :20: , :23B: , :32A: , :33B: , :50K: , :53A: , :57D:
the field i mention in my question has the long string :
:20:111111251540002D
:23B:CREASDD
:32A:09A1237CHF41912,05
:33B:CHASD1912,05
:50K:/CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
:53A:UBSWCHZH80A
:57D:SFS SDFSDFS
RASDASDA
BRANCH 000
GOGOGOG
:59:/43434
DFSDFL GRSDFOUP
SDFDF + DF LTFDD
DFD HDFDN 343423
GOGOER
:71A:OER
that i would like to split into many new fields,and the delimiter should me -> :numeronly OR numer&Char:
as you can see : :20: , :23B: , :32A: , :33B: , :50K: , :53A: , :57D:
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<that i would like to split into many new fields,and the delimiter should me -> :numeronly OR numer&Char:>>
So you can't count on the colon always being there as the start of a new section or was that a typo? Did you mean to type this:
and the delimiter should me -> :numeronly: OR :numer&Char:
If you will always have a leading/trailing colon, then use Split().
Token one then will be the code, token two the value for it (you'll have two entries in the array for each element; 1 & 2 first code and value, 3 & 4 second code and value, 5 & 6 third code and value, etc).
Jim.
So you can't count on the colon always being there as the start of a new section or was that a typo? Did you mean to type this:
and the delimiter should me -> :numeronly: OR :numer&Char:
If you will always have a leading/trailing colon, then use Split().
Token one then will be the code, token two the value for it (you'll have two entries in the array for each element; 1 & 2 first code and value, 3 & 4 second code and value, 5 & 6 third code and value, etc).
Jim.
i don't understand the result you want to get..
what is the problem with my query ?
what is the problem with my query ?
try this query
select f1, IIf(InStr([f1],":")>0,Left ([f1],InSt rRev([f1], ":")),"") as Result1, IIf(InStr([f1],":")>0,Mid( [f1],InStr Rev([f1]," :")+1),[f1 ]) as Result2
from yourTable;
select f1, IIf(InStr([f1],":")>0,Left
from yourTable;
ASKER
Nick67 YOU ALmost reach the point, still need small fix :
IN :
ResultID UglyID DelimiterValue UglyText
5 1 :50K: /CH110023412400243526
in table tblResults , should contain :
UglyText
/CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
and not ONLY : /CH110023412400243526
means should contain the string until the next delimiter " :53A:"
IN :
ResultID UglyID DelimiterValue UglyText
5 1 :50K: /CH110023412400243526
in table tblResults , should contain :
UglyText
/CH110023412400243526
MSFSDFER KFSFUTDFR
BUDFSFERSTR. 2
8113 BOSDFSFSLSEN
and not ONLY : /CH110023412400243526
means should contain the string until the next delimiter " :53A:"
@drtopserv
Have you tested or have any questions about my solution?
Have you tested or have any questions about my solution?
Easily enough fixed.
Ugly text just has to be held and concatenated until the next colon is encountered and THEN written to the table. You need me to fix that, or do you have it cased?
Ugly text just has to be held and concatenated until the next colon is encountered and THEN written to the table. You need me to fix that, or do you have it cased?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You all are great helpers