5 pts.
InStr function
I need to get cities' name from a format like this: Los Angeles, CA New Orleans, LA etc.. I've tried: substr(a.air_location, -4) but that gives me the opposite...", LA" Any suggestions? Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try this:


You may also want to try this…. establish length of string, read back by character until char=,. Subtract number of char read from original length. City name = orig len-char read.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Kccrosser
    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.
    3,830 pointsBadges:
  • carlosdl
    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".
    85,865 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: