on 04-02-2013 2:53 PM
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.
Hi Bill,
Try this:
stringvar str;
str := {string_field_from_database};
strreverse(mid(strreverse(str),instr(strreverse(str)," ")+1, len(str)))
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.