Select by attribute: String Concatination

[LyonMNGIS]

Hello,

I am trying to build an SQL statement to find records where two concatenated fields are equal to a third field.  Specifically I would like to build the following statement

CONCAT(field1, " ", field2) = field3.

Is this possible?

Thanks!

[CodyPatterson]

Hey Lyon,

 This is valid SQL from the looks of it, I've just attempted this exact CONCAT function and it had worked with me. Specifically like this:

SELECT *
FROM TestTable
WHERE CONCAT(field1, ' ', field2) = 'Work Flow';

I believe this is what you're asking!

[LyonMNGIS]

@CodyPatterson

I am still having trouble.  The SQL you have looks perfect for regular SQL statements, but is complaining when I try to create a definition query or select by attribute 😞

This works:

CONCAT(ST_NAME, LOCATION) = ROUTE_NUM

This does not work when I try to add a space:

CONCAT(ST_NAME, ' ', LOCATION) = ROUTE_NUM

Thank You

[CodyPatterson]

Hey Lyon,

 Sorry for the late response, you may try giving this a shot, I've heard that ampersands work as well:

field1 & ' ' & field2 = field3

This is without CONCAT as well, could possible get somewhere, but I will keep looking if that doesn't end up working!

[LyonMNGIS]

Cody,

I figured it out.  ArcGIS does not understand the regular + character when we want to concatenate a field value with a string.  But it does understand a double pipe such as:

ST_NAME || ' ' || ST_POS_TYP = ST_CONCAT

Thank You

[JoshuaBixby]

Specific support for SQL syntax and operators varies depending on the back-end data source.  Although Esri doesn't standardize some syntax and operators at the software level, anything beyond the most simple can vary depending on the data source, so it is always good to state what data source or workspace you are working with.