Here is a micro blog which I'm sure all the hardened SQL people out there will roll their eyes and say so what, knew that? Well I did not and wanted to share this nugget. This is for a desktop scenario.
I have a file geodatabase table, one field is numeric and only holds a single integer, the second field is text and holds a comma separated set of integers, example shown below:
I want to select rows where the numeric value in field1 is found in the text held in field2. So in the example above I want to select rows 2,4 & 5.
Using the Select by Attribute tool from the menu I worked out that the SQL expression I needed to write was this:
Field2 LIKE'%'|| CAST( Field1 AS CHAR(1))||'%'
After finally reading the help file I discovered the special || characters are what is required to concatenate text within a #SQL expression. The desktop help was also not clear that you need to specify the number of characters you want to cast.