topic Re: Select by attribute: String Concatination in ArcGIS Pro Questions

Select by attribute: String Concatination

LyonMNGIS — Mon, 11 Dec 2023 16:55:01 GMT

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!

Re: Select by attribute: String Concatination

CodyPatterson — Mon, 11 Dec 2023 17:07:22 GMT

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!

Re: Select by attribute: String Concatination

LyonMNGIS — Mon, 11 Dec 2023 17:36:27 GMT

@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

Re: Select by attribute: String Concatination

CodyPatterson — Mon, 11 Dec 2023 20:02:41 GMT

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!

Re: Select by attribute: String Concatination

LyonMNGIS — Fri, 15 Dec 2023 14:25:54 GMT

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

Re: Select by attribute: String Concatination

JoshuaBixby — Mon, 18 Dec 2023 15:22:25 GMT

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.