I have a SQL Server table that I have imported from a Text file. In this process, a bunch of the entries have quote marks leading and trailing the entry of several data rows. For example the the table ‘tblBlog’ with colunms : Site, Owner, Partner.
The sample row of text file like below corresponds with the columns of tblBlog :
‘4rapiddev.com’ ‘[email protected]’ ‘[email protected]’
Now, before importing to database, I need to remove leading and trailing quotes(‘) of these fields.
There are many way to solve, for example you can use C# function SPLIT and REPLACE
In this article, I would like to introduce to you the way of removing leading and trailing quotes(‘) or double quotes(“) by using C# Regular Expression of namespace System.Text.RegularExpressions
1. Default.aspx – Design HTML layout
A text box to input data, a button to remove removing leading and trailing quotes(‘) and a button to remove removing leading and trailing double quotes(“). Please see the image at the bottom of this article for more detail.
<form id="form1" runat="server"> <div> <strong>Input Data : </strong><br /> <asp:TextBox ID="txtData" runat="server" text="" TextMode="MultiLine" Rows="5" Width="400px"> </asp:TextBox> <br /> <asp:Button ID="btnRemoveQuote" runat="server" Text="Remove Quotes" onclick="btnRemoveQuote_Click"/> <asp:Button ID="btnRemoveDoubleQuote" runat="server" Text="Remove Double Quotes" onclick="btnRemoveQuote_Click"/> <br /><br /> <strong>Data for testing :</strong> <br /> <asp:Label ID="lblQuotes" Text="'4rapiddev.com' '[email protected]' '[email protected]'" runat="server"></asp:Label> <br /> <asp:Label ID="lblDoubleQuotes" Text='"4rapiddev.com" "[email protected]" "[email protected]"' runat="server"></asp:Label> </div> </form> |
2. Default.aspx.cs – Create code behind to process pressing button “Remove Quotes” and “Remove Doubles Code”
The main point of this block code is to declare function GetData based on two parameters : input data and pattern. We use Regular Expression to match input data with input pattern and return list of corresponding strings.
++ Pattern “‘(.*?)'” : match with data between quotes(‘).
++ Pattern \”(.*?)\” : match with data between doublquotes(“).
using System.Text.RegularExpressions; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { this.txtData.Text = this.lblQuotes.Text; } } protected void btnRemoveQuote_Click(object sender, EventArgs e) { Button button = sender as Button; List<string> lstValue = new List<string>(); if (this.btnRemoveQuote.Text.Equals(button.Text)) // Process pressing button "Remove Quotes" { lstValue = GetData(this.txtData.Text, "'(.*?)'"); } else if (this.btnRemoveDoubleQuote.Text.Equals(button.Text)) // Process pressing button "Remove Double Quotes" { lstValue = GetData(this.txtData.Text, "\"(.*?)\""); } // Display result Response.Write("<strong>Result:</strong><br/>"); Response.Write("======================================================<br/>"); if (lstValue.Count == 0) Response.Write("<strong>No data found!</strong><br/>"); for (int i = 0; i < lstValue.Count; i++) { Response.Write(string.Format("{0} : {1}<br/>", i + 1, lstValue[i])); } Response.Write("======================================================<br/>"); } /// <summary> /// Get list of strings of input data based on input pattern. /// </summary> /// <param name="text">Input data</param> /// <param name="pattern">quote patter :"'(.*?)'" or double pattern : "\"(.*?)\"</param> /// <returns>List of strings</returns> List<string> GetData(string text, string pattern) { List<string> retList = new List<string>(); Regex rex = new Regex(pattern); MatchCollection mc = rex.Matches(text); foreach (Match m in mc) { if (m.Success) { retList.Add(m.Groups[1].ToString()); } } return retList; } |