DataView Row Filter Based On Pattern Matching Column(s)

Tested On: VS Version: 2002 and 2003 .NET Frakework Version 1.1 and 1.0 Problem: I am trying to filter a set of records retreived from the database based on altering portions of a single column. For Example: Consider a column, ItemNo to contain values such as C141XX00220052CGVK. The 18 character Item No. consists of diferent meaningful coded parts (as in the figure above). We have: 1) One column storing ItemNo as varchar (SQL Server 2000) 2) A need to flexibly search and filter records as if each part of the item number were in its own column. Solution 1: Use T-SQL LIKE clause. Problem: LIKE clause does not allows wildcards in the middle of the string. For example: *C14* - is valid C34* - is valid *C4*K4* - is invalid My Solution: 1) Construct a RegEx regular expression based on the pattern of values (item no. parts) supplied 2)Traverse the DataRowViewCollection of the Defaul DataView object 3)Use RegExp.IsMatch() method to compare the (ItemNo) column value with the regular expression for each DataRow object in the collection 4)Mark rows for delete 5) Delete row(s) [Source Code Below] 'Simply call doDeepFilter function with column name, and regular expression to match. Then bind the DataView/DataSet object to any data live control. Me.doDeepFilter("itemNo", getFilterQuery()) rptProductionList.DataSource = dvList rptProductionList.DataBind() --------------------------------------------------- Private Sub doDeepFilter(ByVal colName As String, ByVal regExp As String) Dim deleteArray As New ArrayList If Me.advanceSearchPanel.Visible Then For i As Integer = 0 To dvList.Count - 1 If Regex.IsMatch(CType(dvList.Item(i).Row.Item(colName), String), regExp) = False Then deleteArray.Add(dvList.Item(i).Row) End If Next End If For Each dr As DataRow In deleteArray dr.Delete() Next End Sub ------------------------------------------------------------------------------------ Private Function getFilterQuery() As String Dim searchExpression As String searchExpression = searchExpression.Concat(searchExpression, ParseItemNoToExpression(Me.txtitemConductor.MaxLength, Me.txtitemConductor.Text.ToUpper)) searchExpression = searchExpression.Concat(searchExpression, ParseItemNoToExpression(Me.txtBuild.MaxLength, Me.txtBuild.Text.ToUpper)) searchExpression = searchExpression.Concat(searchExpression, ParseItemNoToExpression(Me.txtFilm.MaxLength, Me.txtFilm.Text.ToUpper)) searchExpression = searchExpression.Concat(searchExpression, ParseItemNoToExpression(Me.txtSize.MaxLength, Me.txtSize.Text.ToUpper)) searchExpression = searchExpression.Concat(searchExpression, ParseItemNoToExpression(Me.txtPackaging.MaxLength, Me.txtPackaging.Text.ToUpper)) searchExpression = searchExpression.Concat(searchExpression, ParseItemNoToExpression(Me.txtClass.MaxLength, Me.txtClass.Text.ToUpper)) searchExpression = searchExpression.Concat(searchExpression, ParseItemNoToExpression(Me.txtCustomer.MaxLength, Me.txtCustomer.Text.ToUpper)) Return "\b" & searchExpression & "\b" End Function --------------------CREATE A REGULAR EXPRESSION DYNAMICALLY-------------- Private Function ParseItemNoToExpression(ByVal characters As Integer, ByVal str As String) As String Dim strBuilder As New StringBuilder If str.Length = 0 Then Return strBuilder.Append("\").Append("w{").Append(characters & "}".Chars(0)).ToString ElseIf str.Length > 0 And str.Length < style="color: rgb(0, 0, 153);">Then For Each chr As Char In str strBuilder.Append("[" & chr & "]") Next Return strBuilder.Append("\").Append("w").Append("{" & characters - str.Length & "}".Chars(0)).ToString Else Return "[" & str & "]" 'return pattern in regular expression End If End Function