I have faced a scenario when I need to clean a user input string and remove some specific words from it but the words are in a database table as comma separated values. I have find the solution for my problem and written a function which will get comma separated values string and user input string as parameters and compare database words with input string words and replace them with empty string one by one and return a clean string. I am going to share this code with you in this article.
1.Create a database and table and insert some words in it
2.Create a new website in Visual Studio 2012 either in VB.NET or C#
<asp:TextBox ID="txtInputString" runat="server">asp:TextBox>
<br />
<asp:Button ID="btnCleanString" runat="server" Text="Button" />
<br />
<asp:Label ID="lblCleanString" runat="server" Text="Label">asp:Label>
3.Write below code in button click event
VB.NET
Protected Sub btnCleanString_Click(sender As Object, e As EventArgs) Handles btnCleanString.Click
Dim Words As String = ""
Dim CleanString As String = ""
Dim InputString As String = txtInputString.Text
Dim myConnectionString As String = "Data Source=Local;Initial Catalog=MyDatabase;Integrated Security=True;Pooling=False"
Dim myConnection As New SqlConnection(myConnectionString)
Dim myQuery As String = "SELECT Words FROM MyDatabase.dbo.MyTable"
Dim myCommand As New SqlCommand(myQuery, myConnection)
myConnection.Open()
Dim myAdapter As New SqlDataAdapter(myCommand)
Dim myTable As New DataTable()
myAdapter.Fill(myTable)
For Each myRow As DataRow In myTable.Rows
Words = myRow("Words")
Exit For
Next
myConnection.Close()
CleanString = CleanStringWithWords(Words, InputString)
lblCleanString.Text = CleanString
End Sub
C#
protected void btnCleanString_Click(object sender, EventArgs e)
{
string Words = "";
string CleanString = "";
string InputString = txtInputString.Text;
string myConnectionString = "Data Source=Local;Initial Catalog=MyDatabase;Integrated Security=True;Pooling=False";
SqlConnection myConnection = new SqlConnection(myConnectionString);
string myQuery = "SELECT Words FROM MyDatabase.dbo.MyTable";
SqlCommand myCommand = new SqlCommand(myQuery, myConnection);
myConnection.Open();
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
foreach (DataRow myRow in myTable.Rows)
{
Words = Convert.ToString(myRow["Words"]);
break;
}
myConnection.Close();
CleanString = CleanStringWithWords(Words, InputString);
lblCleanString.Text = CleanString;
}
4.Write below function in your file to clean string
VB.NET
Public Shared Function CleanStringWithWords(ByVal Words As String, ByVal InputString As String) As String
Dim CleanString As String = InputString
Dim arrValues1() As String = Split(Words, ",")
Dim arrValues2() As String = Split(InputString, " ")
Dim ThisValue1 As String = ""
Dim ThisValue2 As String = ""
Dim Counter As Int32 = 0
Dim Counter2 As Int32 = 0
For intCounter1 As Int32 = 0 To arrValues1.Length - 1
ThisValue1 = arrValues1(intCounter1)
For intCounter2 As Int16 = 0 To arrValues2.Length - 1
ThisValue2 = arrValues2(intCounter2)
If ThisValue1.ToLower.Trim = ThisValue2.ToLower.Trim Then
CleanString = Replace(CleanString, ThisValue2, "")
End If
Counter2 += 1
Next
Counter += 1
Next
CleanString = CleanString.Replace(" ", " ")
CleanString = Trim(CleanString)
Return CleanString
End Function
C#
public static string CleanStringWithWords(string Words, string InputString)
{
string CleanString = InputString;
string[] arrValues1 = Words.Split(',');
string[] arrValues2 = InputString.Split(' ');
string ThisValue1 = "";
string ThisValue2 = "";
Int32 Counter = 0;
Int32 Counter2 = 0;
for (Int32 intCounter1 = 0; intCounter1 <= arrValues1.Length - 1; intCounter1++)
{
ThisValue1 = arrValues1[intCounter1];
for (Int16 intCounter2 = 0; intCounter2 <= arrValues2.Length - 1; intCounter2++)
{
ThisValue2 = arrValues2[intCounter2];
if (ThisValue1.ToLower().Trim() == ThisValue2.ToLower().Trim())
{
CleanString = CleanString.Replace(ThisValue2, "");
}
Counter2 += 1;
}
Counter += 1;
}
CleanString = CleanString.Replace(" ", " ");
CleanString = CleanString.Trim();
return CleanString;
}
5.Now start debugging, enter some string and see the result