topic Re: Definition Query to find where first character = 'XYZ' in Mapping Questions

Definition Query to find where first character = 'XYZ'

BenVan_Kesteren1 — Tue, 07 Feb 2017 05:44:06 GMT

Hi All,

Just wondering if there is an easy way in the Query Builder to create a definition Query where the attributes first character starts with X??

Just for an example, in the below screenshot how would one create a query to return all features where the Feature_Type starts with the letter 'Y'?? At this stage it would only show YARRAMBA CRES. and YARRUNGA.

I was thinking something like using LEFT(FEATURE_TYPE,1) = 'Y' but this is invalid, I am assuming there is some other easy way that I cannot think of at 5pm in the afternoon!

#sql‌ #definition query.‌

Any suggestions?

Re: Definition Query to find where first character = 'XYZ'

FC_Basson — Tue, 07 Feb 2017 05:50:52 GMT

FEATURE_TYPE LIKE 'Y%'

FEATURE_TYPE LIKE 'YAR%'

The % is used as a wildcard and can be placed behind or in front of the text you want to match

e.g. FEATURE_TYPE LIKE '%CRES'

Re: Definition Query to find where first character = 'XYZ'

JayantaPoddar — Tue, 07 Feb 2017 05:53:47 GMT

Try this:

"FEATURE_TYPE" LIKE 'Y%'

Re: Definition Query to find where first character = 'XYZ'

BenVan_Kesteren1 — Tue, 07 Feb 2017 05:54:53 GMT

I knew it was too close to finish time to be trying to think of something simple like that! haha

Thanks!

Re: Definition Query to find where first character = 'XYZ'

RichardFairhurst — Tue, 07 Feb 2017 15:39:30 GMT

The Like and wildcard expression is the easiest to use if you want to match a given portion of a set string. However, there is a way to write code that is like your original approach, but the expression is not LEFT, it is SUBSTRING (at least for file geodatabases and shapefiles). The basic syntax is:

SUBSTRING(string_exp FROM start FOR length)

So for your problem it would be:

SUBSTRING(FEATURE_TYPE FROM 1 FOR 1) = 'Y'

Your title suggested you wanted to find any string that began with X, Y or Z. That could be done with this expression:

SUBSTRING(FEATURE_TYPE FROM 1 FOR 1) IN ('X', 'Y', 'Z')