Definition Query to find where first character = 'XYZ'

[BenVan_Kesteren1]

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?

 

[FC_Basson · Accepted solution]

FEATURE_TYPE LIKE 'Y%'

OR

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'

[BenVan_Kesteren1]

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

Thanks!

[JayantaPoddar]

Try this:

"FEATURE_TYPE" LIKE 'Y%'

[RichardFairhurst]

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')