cancel
Showing results for 
Search instead for 
Did you mean: 

Finding the first space in a string starting from the right of the string

Former Member
0 Kudos

I have the following string "Chicago, ILL, 68001 USA" and I want to bring everything back from that string and put it in a variable with the exception of the USA. How do I exclude the USA. Just so that I'm clear, the end result that I want as my string is "Chicago, ILL, 68001". (I don't need the quotations) The USA could be any country, it will not always be 3 characters long. What I think I would need to do is start from the right and search for the first space going from right to left but I'm not sure how to do that.

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Bill,

Try this:

stringvar str;

str := {string_field_from_database};

strreverse(mid(strreverse(str),instr(strreverse(str)," ")+1, len(str)))

-Abhilash

Former Member
0 Kudos

Thank you Abhilash, it worked great.

Former Member
0 Kudos

Abhilash,

I was incorrect. It worked for the example that I gave with the Chicago, ILL address but the thing that I did not mention was that I want this to work for any address. When I put in a longer address I did not get the right string back.

Here's the address that I used:

LongercitythanChicago, ILL, 68136 American Samoa

This is what I got back using your formula:

Longercitythanch

Instead of:

LongercitythanChicago, ILL, 68136

abhilash_kumar
Active Contributor
0 Kudos

Hi Bill,

That's strange. For the same string I get:

LongercitythanChicago, ILL, 68136 American

Increase the size of field or right-click the field > format field > Common tab > Check "Can Grow" option.

-Abhilash

abhilash_kumar
Active Contributor
0 Kudos

Also, in the above string if "American Samoa" is the string you're trying to get rid of, then the formula I suggested above will not work as it only scans the 1st space from the right and replaces everything after that.

The best solution in this scenario would be to build an array with all possible country names and then search the database field to find if it contains the values in the array. Here's what I would do:

1) Build the array. Create this formula and place it on the Report Header:

stringvar array arr;

arr := ["American Samoa","India","USA","England","France","Italy"];

""

2) Create another formula for searching the database field:

stringvar str;

stringvar array arr;

stringvar fin_str;

str := "LongercitythanChicago, ILL, 68136 American Samoa"; //Replace the string with the database field

numbervar i;

for i := 1 to ubound(arr) do

(

    if instr(str,arr[i]) > 0 then

    (

        fin_str := replace(str, arr[i],"");

    )

);

fin_str;

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

I expanded the field and you were right, I got the same thing you did but I noticed that I should look at this differently. If I have a country like American Samoa then things are not going to be correct. Simply put I don't want anything after the zipcode, which in my example is 68136. In my address there's actually only one comma and that's after the city so would it be possible to write a formula that would check for the third space after the comma and print everything to the left of that space? Thank you for your help on this.

abhilash_kumar
Active Contributor
0 Kudos

You read my mind (Or rather I read yours)! Please try the formula I posted above.

-Abhilash

abhilash_kumar
Active Contributor
0 Kudos

And you can build a formula that checks the nth comma/space, however that will only complicate matters and might not produce the right results always, unless you're a 100 percent sure that the addresses will always be in this format:

xxxxxxxxx, xxxx, 1232323 American Samoa

-Abhilash

Answers (0)