It's likely that there isn't a better solution, I have a workaround here and this should be involved to SQL SELECT statement. First I have to change the SELECT statement to include a hidden column, this column will contain only the last word of the visible column and this column is invisible when filling data in DataGridView. The filter will apply on this hidden column instead of the visible column.
Here are details:
SELECT REVERSE(RTRIM(SUBSTRING(REVERSE(Fullname),1,CHARINDEX(' ',REVERSE(Fullname))))) [Name] FROM MyTable
Suppose I have a DataGridView named gridView, then after assigning its datasource, we make the column 'Name' invisible:
gridView.Columns["Name"].Visible = false;
And the last, we apply the Filter on the hidden column (Name):
myBindingSource.Filter = "[Name] like 'variableText%'";
The line of code above will be placed in some TextChanged event handler, user will change the text by typing in some textbox and the Filter will change accordingly. This works great however it's not a direct solution, it's a workaround, I would like someone to give me the direct solution.