ArcMap SQL Trim Def query code

Question

How can I get this ArcMap SQL Trim Def query code correct?

I have a attribute field that looks like: LOC: R1, THEN A BIG LONG BUNCH OF WORDS or LOC: L10, THEN A BIG LONG BUNCH OF WORDS

What I need to display on the label for the asset is just R1 or L10 (bearing in mind these answers are different per asset e.g. R1, R2, R3, R4, R5, R6, R7, R8, R9, R10 and then the same for the L's.

All my searches online keep finding regular SQL answers which ArcMap doesn't like.

Basically I don't want to see LOC: or everything after L10 (for example) till it runs out of words.

Here's where I stopped and tried more web searching for a better answer

LTRIM(COLUMN_NAME, 1, 5) AND RTRIM(COLUMN_NAME, 9, 250)

What can I do?

• PolyGeo: Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Please check out our short tour to learn about our focussed Q&A format. You say in your title and first question that you are using a "Def query" which I would have assumed meant "Definition Query" but then you mention labels. Are you trying to write a Definition Query or a Label Expression?

Answer 1

If the format is as consistent as your question suggests an advanced python expression alone might work where [LOC_DATA] is replaced with your actual column name.

def FindLabel ( [LOC_DATA] ):
 x= [LOC_DATA] 
 return x[x.find(":")+2:x.find(",")]

This is really going to boil down to how consistent the data is when it comes to colons, commas, and spaces.