String Manipulation 1

[abrudtkuhl]

Thank you in advance for help..

This is simple, but I cannot get it to work. I am looping through a function that migrates data from one db to another. I have a string that is a list all the columns in the table. I need to remove the first column from the string everytime it loops.

Original string:
strColVal = "ID, name, etc, etc, etc"

Desired string:
strColVal = "name, etc, etc, etc, etc"

The column name I want to get rid of is not the same length everytime, so I need to use the first comma as a delimiter. Any help would be greatly appreciated. Thanks

 

[bmdb]

Look into using the Split function.

 

[EBGreen]

strColVal = "ID, name, etc, etc, etc"
strColVal = Replace(strColVal, Left(strColVal, Instr(strColVal, ",") + 1), "")
MsgBox strColVal

Watch for line breaks.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[percent]

Private Function GetToken(ByVal strVal As String, intIndex As Integer, strDelimiter As String) As String
Dim strSubString() As String
Dim intIndex2 As Integer
Dim i As Integer
Dim intDelimitLen As Integer

intIndex2 = 1
i = 0
intDelimitLen = Len(strDelimiter)

Do While intIndex2 > 0

ReDim Preserve strSubString(i + 1)

intIndex2 = InStr(1, strVal, strDelimiter)

If intIndex2 > 0 Then
strSubString(i) = Mid(strVal, 1, (intIndex2 - 1))
strVal = Mid(strVal, (intIndex2 + intDelimitLen), Len(strVal))
Else
strSubString(i) = strVal
End If

i = i + 1

Loop

If intIndex > (i + 1) Or intIndex < 1 Then

MsgBox "error"

Else

GetToken = strSubString(intIndex - 1)

End If

End Function

this function will get a string that is separated with a comma you would use it like this

Result = GetToken(YourInputStr, 1, ",")

the 1 indicates which section to retreive

for example

if YourInputStr="123,456,789"
and you left the 1 then the Result="123"

if YourInputStr="123,456,789"
and you changed the 1 to a two then the Result="456"






%, 2004

 

[johnwm]

Percent, suggest you check out the intrinsic Split() function!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[rsinj]

TomThumbKP, only problem with your solution is that Replace will replace all occurrences of the substring, which would cause incorrect results if for example,

strColVal = "ID, name, UserID, etc, etc"

using your solution as a starting point, without using Replace, something better would be:

strColVal = Right(strColVal, Len(strColVal) - Instr(strColVal, ","))

 

[EBGreen]

You are correct that the solution would not be generic, however it will work for the question asked because the list is a list of the column names for a table. Since column names must be unique within a table, this isn't a concern.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[abrudtkuhl]

Thank you all for your responses. I have yet to try as I just got into the office. But I will try these out. Thanks for all your help!

 

[EBGreen]

rsinj, please ignore my prior post. You are correct my solution is not the correct one. In this case:
strColVal = "ID, name_ID, etc, etc, etc"
It would not work correctly. This corrects that:

strColVal = "ID, name_ID, etc, etc, etc"
strColVal = Replace(strColVal, Left(strColVal, Instr(strColVal, ",") + 1), "", 1, 1)
MsgBox strColVal

Thanks for the catch.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[abrudtkuhl]

Update: I used tomThumb's solution and it works great. thank you all.

 

[VBrit]

There is a much faster way to do this:

mid$ (sourcestring ,(instr(1,"," , sourcestring) +1))

 

[EBGreen]

That only returns the first value. He needs to pop the first value off of the stack.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]