While that will work, personally I don’t like to hard-code substrings that may depend on how text was entered. If there are extra spaces or if the state/region name is more or less than 2 characters, this will result in corrupted results.

I would code this like:

decode(instr(a.air_location, ‘,’), 0, a.air_location, rtrim(substr(a.air_location, 1, instr(a.air_location, ‘,’)-1)

This expression evaluates as:
If there is no comma in the a.air_location string, return the full string, else return everything to the left of the comma, with any trailing spaces stripped.

This will produce the following results:

Los Angeles, CA -> “Los Angeles”
Los Angeles , CA -> “Los Angeles” (simple expression would return “Los Angeles ” (space at end)
Los Angeles, CAL -> “Los Angeles” (simple expression would return “Los Angeles,” (comma included)
Los Angeles CA -> “Los Angeles CA” (simple expression would return “Los Angele”

Note that when using “decode” with an expression that returns a variable number, the easy way is look for the “fail” condition. In this case, look for a zero return, indicating that the “instr” function didn’t find a comma in the source text.

You are absolutely right. It is not recommended to hard-code positions if there is at least a remote chance that the information is not standarized.

Good point.

P.S. The problem when splitting un-standarized strings is that you have no way to guarantee that the results will alwaysbe correct, for example, getting “Los Angeles CA” when you would want to get “Los Angeles”.