Given a string variable named quxx, that falls in the set of [A-Z,#,ALL]:
I wanted to use the DataView RowFilter property to filter data for use in a custom alphanumeric paging control (All, A-Z, and Numbers). The RowFilter expression for the A-Z characters was very straightforward:
dataView.RowFilter = string.Format("{0} LIKE '{1}%'", dataView.Table.Columns[0].ColumnName, quxx);
So, extending that to filter on things that start with a number, I looked at the TSQL syntax, figuring it would be similar and tried:
dataView.RowFilter = string.Format("{0} LIKE '[0-9]%'", dataView.Table.Columns[0].ColumnName, quxx);, but got an invalid expression exception.
The way I solved it was to use SubString on the first character in the string (note it is one-based and not zero-based):
dataView.RowFilter = string.Format("SUBSTRING({0}, 1, 1) IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)", dataView.Table.Columns[0].ColumnName, quxx);
Thanks http://www.whoisjake.com for the alphanumeric pager control code :)
