I inherited a database where user input fields are stored as a comma delimited string. I know. Lame. I want a way to parse these fields in a SELECT query where there are three segments of varying number of characters. Counter to all the recommendations that I insert fields into a new table or create a stored procedure to do this, this is what I came up with. I'm wondering if anyone sees any flaw in doing this as a select query (where I can easily convert from string to parsed and back again as need be).
Field_A
5,25,89
So to get the left segment, which is the most straightforward:
Field_1: Left$([Field_A],InStr([Field_A],",")-1)
To get the right-most segment:
Field_3: Right$([Field_A],Len([Field_A])-InStrRev([Field_A],","))
Middle segment was the trickiest:
Field_2: Mid([Field_A],InStr([Field_A],",")+1,InStrRev([Field_A],",")-InStr([Field_A],",")-1)
So the result is:
Field_1 Field_2 Field_3
5 25 89
Any consenting opinions?
Well, if you insist on going down this road...... This might be easier and more adaptable. Create a function in a module:
Public Function GetValueFromDelimString(sPackedValue As String, nPos As Long,
Optional sDelim As String = ",")
Dim sElements() As String
sElements() = Split(sPackedValue, sDelim)
If UBound(sElements) < nPos Then
GetValueFromDelimString = ""
Else
GetValueFromDelimString = sElements(nPos)
End If
End Function
Now in your query you can get any field in the string like this: GetValueFromDelimString([MultiValueField],0) AS FirstElement, GetValueFromDelimString([MultiValueField],1) AS SecondElement, etc.
I feel like I am buying beer for a minor, encouraging this type of behavior :)