CODEWITHSUNDEEP
vbscriptasp-classic
compcobalt
compcobalt

Reputation: 1362

Replace a comma in between two double quotes

I would like to replace a comma in between two double quotes.

EXAMPLE:

Replace:

11/18/2015,15:27,1,103,3,,,197179,"Petco, Inc.",Amy,Jr,187.061,452.5,0,0,0,2.419,0,0,37.38,489.88`

With:

11/18/2015,15:27,1,103,3,,,197179,"Petco Inc.",Amy,Jr,187.061,452.5,0,0,0,2.419,0,0,37.38,489.88

NOTE: I still want to keep the bare commas I just want to replace any commas that are inside of the double quote "

I know I can replace the commas by doing this: strText = Replace(strText, ",", "")

but how do I do that in between the two double quotes only and not affect the other commas that are outside of the double quotes.

Tried this thanks to pee2pee but getting an error: Expected identifier 

.Pattern    = "/(".*?"|[^",\s]+)(?=\s*,|\s*$)/g"
-------------------^


  dim re
  Set re = New RegExp
  With re
      .Pattern    = "/(""".*?"""|[^""",\s]+)(?=\s*,|\s*$)/g"
      .IgnoreCase = False
      .Global     = False
  End With
  Set re = Nothing

Thanks

Upvotes: 1

Views: 2392

Answers (4)

NK_loco
NK_loco

Reputation: 84

An easy (but probably not that efficient) solution could be going one char at a time changing the comma if you passed an uneven amount of double quotes, finishing after the last double quote.

string origin = ""; //Your string to convert
int lastIndex = origin.LastIndexOf('"');
bool betweenQuotes = false;

for(int i = 0; i < lastIndex; i++)
{
  if(origin[i] == '"')
    betweenQuotes = !betweenQuotes;

  if(origin[i] == ',' && betweenQuotes)
    origin[i] = ' ';
}

Edit: You can't set a char like I did in the last row 

origin[i] = ' ';

it won't work in c#. put in that line this instead

origin = origin.Substring(0, i) + " " + origin.Substring(i + 1);

That solution was for C#, so I made a code in VB that does the same thing since as I understood online ASP works (or at least can work) with VB. (Sorry, but it's my first time working with classic asp and VB). But this code in VB is working for me.

Dim origin As String = "11/18/2015,15:27,1,103,3,,,197179,fPetco, Inc.f,Amy,Jr,187.061,452.5,0,0,0,2.419,0,0,37.38,489.88"
Dim lastIndex As Integer = 0
Dim betweenQuotes As Boolean = False


For i As Integer = 0 To origin.Length - 1
    If origin(i) = "f" Then
        lastIndex = i
    End If
Next

For i As Integer = 0 To lastIndex
    If origin(i) = "f" Then
        betweenQuotes = Not betweenQuotes
    End If

    If origin(i) = "," AndAlso betweenQuotes Then
        origin = origin.Substring(0, i) + " " + origin.Substring(i + 1)
    End If
Next

I just replaced the double quotes with the f char since I have no idea how to put special chars in a string in VB. but this code replaces the comma only between the Fs, so it should be ok.

I also made a JS version, cause that would work everywhere on the web doesn't matter what language you used to create the page.

var origin = "";
         var lastIndex = 0;
         var betweenQuotes = false;

         for (var i = 0; i < origin.length ; i++)
             if (origin[i] == '"')
                 lastIndex = i;

         for(i = 0; i < lastIndex; i++)
         {
             if (origin[i] == '"')
                 betweenQuotes = !betweenQuotes;

             if (origin[i] == ',' && betweenQuotes) {
                 origin = origin.substr(0, i) + " " + origin.substr(i + 1, origin.length - i - 1);
             }
         }

Upvotes: 0

Kul-Tigin
Kul-Tigin

Reputation: 16950

1 - Classic one:

csv = "..." ' your lines
ReDim chars(Len(csv) - 1) 'array for output
wearein = False
For i = 1 To Len(csv)
    chars(i-1) = Mid(csv, i, 1)
    Select Case chars(i-1)
        Case Chr(34) 'we're in
            wearein = Not wearein
        Case ","
            If wearein Then chars(i-1) = ""
    End Select
Next
newstr = Join(chars, "")
Response.Write newstr

2 - By using RegExp and a callback function :

Function ReplaceCallback(match, position, all)
    ReplaceCallback = Replace(match, ",", "")
End Function

Set re = New RegExp
    re.Pattern = """[^""]*,[^""]*"""
    re.Global = True

csv = "..." 'your lines
newstr = re.Replace(csv, GetRef("ReplaceCallback")) 'replace done
Response.Write newstr

3 - By mixing MS JScript and VBScript:

<script language="JScript" runat="server">
function removeCommas(text){
    return text.replace(/"[^"]*,[^"]*"/g, function(){return arguments[0].replace(/,/g, "")});
}
</script>
<%
csv = "..." 'your lines
Response.Write removeCommas(csv)
%>

Upvotes: 4

Vixed
Vixed

Reputation: 3509

ONLY and ONLY in this case, you can use:

strText = Replace(strText, ", ", " ")

because you have just a space after the comma you need to replace.

Upvotes: 0

pee2pee
pee2pee

Reputation: 3792

The regex pattern you need would be something like

/(".*?"|[^",\s]+)(?=\s*,|\s*$)/g

Upvotes: 1

Related Questions