This article gives brief overview of how $csearch function works and how it is different from the wildcards used in SQL.

Description of $csearch function:

Selects a number of columns from a data table using a limiting search expression. The first argument is a data table and the second argument is a string that contains the search expression determining which column names should be returned. The function returns a list of the (unescaped) column names from the data table that match the search expression.

Now, let us say we have the list of following columns in our analysis:
 

[Column], [ColumnTest], [Column_Test], [TestColumn], [Test_Column], [Test Column], [Column Test], [Column1], [Column_2], [Column 3], [4Column], [5_Column] and [6 Column]

So, for '$csearch([Data Table],"Column*")', the result we get is:
 

Column,ColumnTest,Column_Test,Column Test,Test Column,Column1,Column_2,Column 3,6 Column

Here, it is unobvious to get [6 Column] and [Test Column] in the result. But this function returns the column names which contain STRING matching with the passed parameter. If your column name contains space, then this function will break it into multiple strings and will try to match the parameter with each and every string.

For example, for [Test Column], this function will break the column name in two strings('Test','Column'). And the passed parameter ("Column*") is matching with 'Column' string, so the [Test Column] will get returned.


For '$csearch([Data Table],"*Column")' we get the following result:
 

Column,Column Test,TestColumn,Test_Column,Test Column,Column 3,4Column,5_Column,6 Column

Similarly, here also [Column Test]  and [Column 3] columns will get returned apart from the other columns.

Wildcards in SQL do not not break the string and consider it as a single string. So for 'Column%' parameter, [Test Column] and [6 Column] will not get returned.

How csearch function works when you have spaces in column names