All,
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.
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:
| ObjectID | Field1 | Field2 |
|---|
| 1 | 2 | 3,4 |
| 2 | 3 | 1,2,3 |
| 3 | 1 | 2 |
| 4 | 1 | 1,2 |
| 5 | 4 | 2,4,5 |
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.
Solution
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.
Hope this helps?
