Select to view content in your preferred language

Select by attribute: String Concatination

763
5
12-11-2023 08:55 AM
LyonMNGIS
Frequent Contributor

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!

 

0 Kudos
5 Replies
CodyPatterson
Frequent Contributor

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!

0 Kudos
LyonMNGIS
Frequent Contributor

@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

 

0 Kudos
CodyPatterson
Frequent Contributor

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!

0 Kudos
LyonMNGIS
Frequent Contributor

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

0 Kudos