SQL Expression: casting, concatenating and searching within

242
0
09-08-2016 08:41 AM
DuncanHornby
MVP Frequent Contributor
0 0 242

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:

ObjectIDField1Field2
123,4
231,2,3
312
411,2
542,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?

About the Author
GIS Desktop Developer I have specialised in river network analysis and mapping but have worked on a wide variety of projects. I am often found at the bottom of a cave!