Select by Attribute where field1 attribute equals field2 and field3 attributes

[PLadd]

In short, below is a query in SQL Server that works and I want to run something similar in ArcGIS Pro.  I don't want to use Field Calculator and add another field.  Can it be done?

SELECT  [STREETNAME], [street], [st_type]

  FROM [db].[dbo].[ADDRESSES]

  where STREETNAME = (street + ' ' + st_type)

Using the following in the Select By Attributes dialog box just says the expression has invalid syntax:

STREETNAME = street + ' ' + st_type

Other efforts include:

STREETNAME = street & ' ' & st_type

And:

STREETNAME = (street + ' ' + st_type)

I even tried:

STREETNAME = CONCAT(street + ' ' + st_type)  - but I think I'm not understanding how to use this properly.

[PLadd]

Interesting!  The above query actually does work on an enterprise geodatabase in ArcGIS Pro but not on a file geodatabase in ArcGIS Pro.  Why is that?

[PLadd · Accepted solution]

Thanks to Rakshanda at ESRI, who was extremely helpful in finding a solution for this issue.  I would never have been able to resolve this through Google searches.

In a file geodatabase, in order to query values in field1 that equal the combined values in field2 and field3 AND (I left out this qualifier) include a space in between field2 and field3, you must use the following syntax:

field1 = field2 || ' ' || field3

The character after field2 and before field 3 is the pipe or vertical bar on the keyboard (shift + \).  You have to use two on either side of the single quotes.