** Function to Parse Commas from a String *** 1

[jonnywah]

I have a string with commas and numbers which can be any number and in any combination. For example:

1, 2, 4, 23, 58
1, 2, , , , , 23, 58
1, , , ,2, 4, 23 , , , , 58 , ,
, , 1, 2, 4, , 23, 58, ,
, 1, 2, 4, , 23, , , 58, ,

Regardless of where the commas are, I want to be able to return a string: 1, 2, 4, 23, 58 (ie. all numbers in the string separated by commas).

Please help ... I am totally stumped. Any suggestions or information would be appreciated. Thank you in advance.

 

[ChrisHirst]

Try this. Could probably be made more efficient as a RegExp but couldn't be bothered working out the pattern.

function CSVString(strIn)
dim temp
dim charArray
dim i
charArray = split(strIn,",")
for i = 0 to ubound(charArray)
	if trim(charArray(i)) <> "" then 
		if i = ubound(charArray) then 
			temp = temp & trim(charArray(i))
		else
			temp = temp & trim(charArray(i)) & ","
		end if
	end if
next
if mid(temp,len(temp),1) = "," then
	temp = left(temp,len(temp)-1)
end if
CSVString = temp
end function

Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[johnwm]

Or:

Public Function fnLoseCommas(strIn As String) As String
Do While InStr(strIn, ",,") > 0
strIn = Replace(strIn, ",,", ",")
Loop
fnLoseCommas = strIn
End 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?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[Tarwn]

Heres a function based on a RegExp:

Function ExtractNumbers(numString)
   Dim regex, matches, match
   Set regex = New RegExp
   regex.Global = True
   regex.Pattern = "\d+"

   Set matches = regex.Execute(numString)
   ExtractNumbers = ""
   For Each match in matches
      ExtractNumbers = ExtractNumbers & match.value & ","
   Next

   ExtractNumbers = Left(ExtractNumbers,Len(ExtractNumbers)-1)
   Set regex = Nothing
End Function

 

[vbkris]

here's one that i wrote that uses replace method of regexp:

str=" , 1, 2, 4, , 23, , , 58, ,"
Set regex = New RegExp
regex.Global = True

regex.Pattern = "(,\s*){2,}"
str=regex.replace(str,",")

regex.Pattern = "\s*,\s*"
str=regex.replace(str,"")

regex.Pattern = "\s*,\s*"
str=regex.replace(str,"")

tarwn:
which is better? replace or execute???




Known is handfull, Unknown is worldfull

 

[Tarwn]

My guess would be that replace is probably going to be the best, since you would be getting rid of the loop and the iterative concatenations. The problem I had was that I was concerned about strings that started or ended with space-comma sequences, because they would need to be replaced with empty strings. You could probably get most of the internals with one replace but then you would need another one to do the ends.

i checked it out, this shouldn't work (you should need to do each end seperately) but it does:

Function ExtractNumbers(numString)
   Dim regex, matches, match
   Set regex = New RegExp
   regex.Global = True

   'all middle non-numerics replaced with single comma
   regex.Pattern = "[^\d]+"
   ExtractNumbers = regex.Replace(numString,",")
   
   'all outside non-numerics replaced with empty
   regex.Pattern = "(^[^\d]+)|([^\d+]$)"
   ExtractNumbers = regex.Replace(ExtractNumbers,"")

   Set regex = Nothing
End Function

I think in most languages if you replaced the beginning of string or end of string char's with something you would get a non-null terminated string, but VBScript was nice enough to let me get away with it.

So basically instead of looking for specific sequences to replace i looked at it from the other angle and told it to replace the largest non-numeric strings it could find with a single comma. Then I went back and told it to replace a leading or trailing comma with an empty string. Seems to work on my test string:
a = ",,,, 1, 2, 4, 23, 58 1, 2, , , , , 23, 581, , , ,2, 4, 23 , , , , 58 , ,, , 1, 2, 4, , 23, 58, , , 1, 2, 4, , 23, , , 58, ,"

Good call vbkris, I should've thought of using Replace on this, I was stuck trying to figure out a one call solution though

-T