Question Filter percent (%) and Apostrophes (')

[mbv]

I am trying to filter fields in a table that contain percent (%) and apostrophe (') characters. I kow that to filter an apostrophe you need to add another apostrophe (''). However, when a text contains the percent (%) character in combination with an apostrophe (') character, an exception is thrown. Also, when using the % character in a filter string, it will be function as an "*" (fields starting or ending with). I need the % to be considered as a character whenever the % makes part of a string in the field. How can I address these issues?

The code below explains my question.

Dim TheTable As New DataTable
Dim col As New DataColumn("Col1")
TheTable.Columns.Add(col)
Dim TheRow As DataRow = TheTable.NewRow
TheRow("Col1") = "Filter me"
TheTable.Rows.Add(TheRow)
TheRow = TheTable.NewRow
TheRow("Col1") = "Filter me%"
TheTable.Rows.Add(TheRow)
TheRow = TheTable.NewRow
TheRow("Col1") = "Filter me'"
TheTable.Rows.Add(TheRow)
TheRow = TheTable.NewRow
TheRow("Col1") = "Filter me%'"
TheTable.Rows.Add(TheRow)
TheRow = TheTable.NewRow
Dim filteredrows() As DataRow

filteredrows = TheTable.Select("Col1 Like 'Filter me'")
MsgBox(filteredrows.Length)

'THE NEXT LINE WILL RETURN ALL RECORDS. I NEED ONLY THE RECORD ENDING WITH (OR CONTAINING) THE CHARACTER %
filteredrows = TheTable.Select("Col1 Like 'Filter me%'")
MsgBox(filteredrows.Length)

'THE NEXT LINE WILL RETURN TWO RECORDS (Filter me) and (Filter me'). I need it to return (Filter me') only
filteredrows = TheTable.Select("Col1 Like 'Filter me'''")
MsgBox(filteredrows.Length)

'THE NEXT LINE WILL THROW AND ERROR: "Error in Like operator: the string pattern 'Filter me%'' is invalid"
filteredrows = TheTable.Select("Col1 Like 'Filter me%'''")
MsgBox(filteredrows.Length)

 

[jmcilhinney]

The general rule in VB is that to escape any special character you simply double it up. To escape a single quote you make it two single quotes. To escape a percent symbol you make it two percent symbols. Etc.

 

[jmcilhinney]

Please post to the most appropriate forum for the topic of your thread. Moved from VS.NET General, which is for general questions about the VS.NET IDE, not the VB.NET language.

 

[mbv]

jmcilhinney. Your suggestion does not work.

From my example, these are the records (I added two more for the purpose of testing all scenarios):

Filter me
Filter me%
Filter me'
Filter me%'
Filter me%Other
Filter me%Other'

Using your suggestion,

filteredrows = TheTable.Select("Col1 Like 'Filter me%%'")

Returns all the 6 records. It should return one record only (Filter me%) as the % is part of the string and not an indicator of "all fields starting with "Filter me"


The lines below still trhow an error

filteredrows = TheTable.Select("Col1 Like 'Filter me%%'''") (Expect one record: Filter me%')

filteredrows = TheTable.Select("Col1 Like 'Filter me%%Other'") (Expect one record: Filter me%Other)

filteredrows = TheTable.Select("Col1 Like 'Filter me%%Other'''") (Expect one record: Filter me%Other')

filteredrows = TheTable.Select("Col1 Like 'Filter me%%*'") (Expect four records: All that start with Filter me%)

 

[jmcilhinney]

OK, let's do what you should have done in the first place then: read the relevant documentation. The documentation for the DataTable.Select method says this:

To create the filterExpression argument, use the same rules that apply to the DataColumn class's Expression property value for creating filters.

The documentation for the DataColumn.Expression property says this:

Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be escaped in brackets ([]). If a bracket is in the clause, the bracket characters should be escaped in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern.

The rule about using a second ampersand applies in some other cases, so I went with that first. In future, I suggest you read the relevant documentation from the MSDN LIbrary first, before anything else.

 

[mbv]

Thanks a lot jmcilhinney. That was exactly what I was looking for

I am more of a google and forums fan and have not been very lucky finding information in MSDN before, but I will give it a try next time. Maybe I just haven't tried hard enough.

 

[jmcilhinney]

Thanks a lot jmcilhinney. That was exactly what I was looking for

I am more of a google and forums fan and have not been very lucky finding information in MSDN before, but I will give it a try next time. Maybe I just haven't tried hard enough.

It doesn't take luck. You just have to use it properly. I recommend using the local library, i.e. the one you access from the Help menu in VS, because it has an index. In this case you simply open the index and type datatable.select into the box and in a click or two you staring at the documentation for the DataTable.Select method. There's no need to search if you already know what you want to see. Once you have the appropriate topic you simply read it and click the appropriate links, in this case the link to the DataColumn.Expression documentation.

If you do have to use the online library then you should still use the search feature as though it was an index. In this case, if you type datatable.select into the search box then the documentation for that method will be at or near the top of the list of results. If you were looking for information on the DataTable class you'd search for datatable class. If you wanted to see the members of the DataTable class you'd type datatable members. It's all just common sense really, particularly when you've used it a few times and you take notice of the titles of the topics you're looking at. Once you see the pattern searching becomes easy.