split(), left(), mid(), right(), something needed like this

I have a variable that basically houses all the fields I will display in an sql query.  Here is the variable for this example:

strFieldsToDisplay = "[INVOICE DETAILS].PART_#, INVENTORYMASTER1.[Mfg Name], INVENTORYMASTER1.Source, [CUSTOMER MASTER].[CUST NAME], [CUSTOMER MASTER].[CUST #], [INVOICE DETAILS].[CUST BUYER NAME], [INVOICE DETAILS].[INVOICE #], [INVOICE DETAILS].[CUST PO #], [INVOICE DETAILS].[CUST ITEM #], [INVOICE DETAILS].PRICE"

I am looking to create a function that will remove everything to the left of the ".".  So, example, [INVOICE DETAILS].PART_# would become PART_#.  The above variable, for end result, would look like this:

strFieldsToDisplay = "PART_#, [Mfg Name], Source, [CUST NAME], [CUST #], [CUST BUYER NAME], [INVOICE #], [CUST PO #], [CUST ITEM #], PRICE"

Any ideas on how to accomplish that?  Using .asp code.
dzirkelbAsked:
Who is Participating?
 
gemartiConnect With a Mentor Commented:
Actually this is better:

'Declare variable
Dim i, text, token, token2, newStr
text = "[INVOICE DETAILS].PART_#, INVENTORYMASTER1.[Mfg Name], INVENTORYMASTER1.Source, [CUSTOMER MASTER].[CUST NAME], [CUSTOMER MASTER].[CUST #], [INVOICE DETAILS].[CUST BUYER NAME], [INVOICE DETAILS].[INVOICE #], [INVOICE DETAILS].[CUST PO #], [INVOICE DETAILS].[CUST ITEM #], [INVOICE DETAILS].PRICE"
token = split(text,",")
For i = 0 To 9
  wscript.echo i & token(i)
  wscript.echo " "
  token2 = split(token(i), ".")
    newStr = newStr & "," & token2(1)
   
Next

wscript.echo newStr
0
 
gemartiConnect With a Mentor Commented:
Maybe

First you would have to use the Split() function to seperate each part of the query where ever a comma occurs.

Then you would have to Split each element of that array to seperate each array element with a "."

For example ("split at each ','):
Split(""[INVOICE DETAILS].
PART_#, INVENTORYMASTER1.[Mfg Name], INVENTORYMASTER1.Source, [CUSTOMER MASTER].[CUST NAME], [CUSTOMER MASTER].[CUST #], [INVOICE DETAILS].[CUST BUYER NAME], [INVOICE DETAILS].[INVOICE #], [INVOICE DETAILS].[CUST PO #], [INVOICE DETAILS].[CUST ITEM #], [INVOICE DETAILS].PRICE", ",")


Give you an array with:

[INVOICE DETAILS].PART_#
INVENTORYMASTER1.[Mfg Name]
INVENTORYMASTER1.Source
[CUSTOMER MASTER].[CUST NAME]
[CUSTOMER MASTER].[CUST #]
[INVOICE DETAILS].[CUST BUYER NAME]
[INVOICE DETAILS].[INVOICE #]
[INVOICE DETAILS].[CUST PO #]
[INVOICE DETAILS].[CUST ITEM #]
[INVOICE DETAILS].PRICE


Then:
for each x array
Split(Array[x], ".")

would give you:


[INVOICE DETAILS]
PART_#
INVENTORYMASTER1
[Mfg Name]
INVENTORYMASTER1
Source
[CUSTOMER MASTER]
[CUST NAME]
[CUSTOMER MASTER]
[CUST #]
[INVOICE DETAILS]
[CUST BUYER NAME]
[INVOICE DETAILS]
[INVOICE #]
[INVOICE DETAILS]
[CUST PO #]
[INVOICE DETAILS][CUST ITEM #]
[INVOICE DETAILS]
PRICE
 

Then you would just concatenate all the odd numbered array2 values since array's start counting a 0.



0
 
gemartiCommented:
Here you go:

'Declare variable
Dim i, text, token, token2, newStr
'store string
text = "[INVOICE DETAILS].PART_#, INVENTORYMASTER1.[Mfg Name], INVENTORYMASTER1.Source, [CUSTOMER MASTER].[CUST NAME], [CUSTOMER MASTER].[CUST #], [INVOICE DETAILS].[CUST BUYER NAME], [INVOICE DETAILS].[INVOICE #], [INVOICE DETAILS].[CUST PO #], [INVOICE DETAILS].[CUST ITEM #], [INVOICE DETAILS].PRICE"
'create array
token = split(text,",")
For i = 0 To 9
  wscript.echo i & token(i)
  wscript.echo " "
  token2 = split(token(i), ".")
    newStr = newStr & "," & token2(1)
   
Next


wscript.echo newStr
0
 
dzirkelbAuthor Commented:
Thanks for your suggestions guys!  I mixed both of your thoughts together to come up with the following function:

Function StripTableName(strData)
      strData = strFieldsToDisplay
      arrayOutputData = Split(strData, ", ")
      intArrayOutputDataCount = uBound(arrayOutputData, 1)
      
      for i = 0 to intArrayOutputDataCount
            arrayOutputDataTemp = Split(arrayOutputData(i), ".")
            
            if i = intArrayOutputDataCount then
                  strOutputData = strOutputData & arrayOutputDataTemp(1)
            else
                  strOutputData = strOutputData & arrayOutputDataTemp(1) &", "
            end if
      next
      
      StripTableName = strOutputData
End Function
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.