Hornbydd

SQL Expression: casting, concatenating and searching within

Blog Post created by Hornbydd on Sep 8, 2016

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?

Outcomes