Extracting all characters of a postcode before the space

[Former Member]

I am trying to extract the first characters from a postcode into a separate field that can then be used as a parameter. If i search on postcodes starting with (for example) M1, this is bring out not only M1 but also M11, M12, etc.

I have found a While Constructs formula example in my training book that extracts the Surname from a field containing the FirstName and Surname. I have recreated this and applied to my postcode field but it is bring out the characters AFTER the space in the postcode and not the characters BEFORE.

Does anybody know how to reverse the following so that it extracts the characters BEFORE the space?

stringVar PCode := {ADDRESS.POSTCODE};
numberVar i:=1;
numberVar Strlen := length (PCode);
numberVar Result:= -1;

while i <= Strlen and Result= -1 do
(
    stringVar Position:= Mid (PCode,i,1);
    if Position = " " then
        Result:= i;
        i:= i + 1;
);
stringVar PostSearch := mid (PCode, Result+1, (Strlen-Result));
PostSearch

[former_member292966 · Accepted solution]

Hi Tracy,

Here's a really simple way to do this:

{ADDRESS.POSTCODE} [1 to (InStr ({ADDRESS.POSTCODE}, " ") - 1)];

The InStr function searches for a space and return the position of the space. This will allow us to extract the characters from position 1 to the (space - 1).

Hope this helps,

Brian

Edited by: Brian Dong on Oct 14, 2008 4:29 PM

[Former Member]

Hi Jaime,

For some reason it is returning a number i.e. 3.00 or 4.00 so I have tried Brian's solution and that has worked. Thanks Brian !! Thanks Jaime!

[Former Member]

I need to do something similar with postcodes but rather than take all characters before the space in the postcode I just need to first letter or letters i.e. M, AB, NN. I then need to group by these postcode keys in a report.

Can anybody assist in writing code that will extract the first letter or letters from standard uk postcodes?

The code below gave me everything prior to the space, which works nicely.

trim(left({ADDRESS.POSTCODE}, instr({ADDRESS.POSTCODE}, " ")))

Many thanks

Tracy

[Former Member]

Since you know that the first part of the UK postcode format is one of: A1 AA1 AA2A or A2A, you can look at the first two characters, and remove any digits from it.

stringvar pc:=trim(left({YourTable.PostcodeField},2));

if isnumeric(right(pc,1))=true

then left(pc,1) else pc

[Former Member]

Thanks for that it works brilliantly. I now have a different problem in that I have a formula field called pcodekey that shows the code of the particular postcode i.e. AB, AL, B. I have grouped by this field and listed the appropriate companies under each group and totalled a field. I have also written a parameter field to select certain postcode keys, and then show only those groups.

This works fine in crystal reporting itself, BUT it brings up a blank report when using Crystal Viewer through an application? Any ideas why this may be?

Regards,

Tracy

[Former Member]

Hmm, afraid not - no real idea! Is it an application that you have written using .net and the Crystal components, or something you've bought/downloaded? I'll see if I can replicate your problem...

Oh, and what's the data source for this (excel, SQL server etc..)?

[Former Member]

It's a CRM application with SQL database back-end. There are reporting menus for internally written reports, but I can also add crystal reports to the menu, which are viewed using crystal runtime. However, when selecting the postcode area to view the report is appearing blank via this method, but opening the report in crystal and refreshing and selecting the parameter works fine.

[Former Member]

Just check that you're not looking at 'null' values on the first page of the report - ie: records that don't have a postcode or whose formula output is an empty string - it might be that they are getting listed first? Probably not, but...

To begin with, I'd start off by removing the postcode-area parameter, and check that all the records are coming out (include the formula in the output). That way we can check that the viewer is evaluating (?) the formula correctly.

You may want to see if the formula/parameter values have leading/trailing spaces - that'd throw it off (esp. if the parameter prompting is done by the application, rather than crystal's default prompting screens.)

As a last resort, I'd create a view in the DB as a datasource for the report, and include the postcode-area formula in that in order to side-step any potential issues. But then again, I do have free reign in my environment...

[Former Member]

I used the second method (recommended).

[Former Member]

See above! Was editing while you posted...

[Former Member]

Jaime,

Thanks for your reply, I have applied your coding. It has partly worked in that it has extracted the Outcode of the postcode as you stated and these are showing correctly. However, I now want to use this PCode field as a selection and parameter so that I have a parameter field looking for the string and I have selected where PCode is equal to the parameter value. I have entered M1 as an example and the following message appears "String length is less than 0 or not an integar".

Any ideas?

[Former Member]

Ah, which method did you use? Either way, I bet you have postcodes that are just the first part, such as "W7" or "E3" and nothing else.

Could revise it to check for a valid length first - if it's not, return the whole string:

if len(replace({ADDRESS.POSTCODE}," ","")) in [5,6,7]
then left(replace({ADDRESS.POSTCODE}," ",""), len(replace({ADDRESS.POSTCODE}," ",""))-3)
else
{ADDRESS.POSTCODE}

Edited by: Jaime Hargreaves on Oct 14, 2008 4:30 PM

Added some checking code

Edited by: Jaime Hargreaves on Oct 14, 2008 4:33 PM

[Former Member]

Hi,

You'd want to use the instr function to find the position of the space, and then do a left() function on the string using that.

However if you are talking about UK postcodes, they always follow the rule of XXX(X)<space>YYY - ie: there are always three characters that represent the 'incode' (after the space) - everything else is the 'outcode' (first part). Using this info, you can extract the first part even where there are no spaces 'coz you've got daft users.

So, two options::

Bit before the space:

trim(left({ADDRESS.POSTCODE}, instr({ADDRESS.POSTCODE}, " ")))

or (recommended)

Get the Outcode (everything before where the sapce should be, regardless of whether it's there or not).

left(replace({ADDRESS.POSTCODE}," ",""), len(replace({ADDRESS.POSTCODE}," ",""))-3)

Further UK postcodes notes:

You may want to run a check that the postcode is valid before doing this (ie: 5 - 7 chars excluding space, final 3 chars are always X##, first part can be X#, X## etc...). Drop a line back if you need a list of things to check.

[Former Member]

Hello Tracy,

I am not sure how to help you on this however I have found an interesting code from one of the forums, see if that makes your life any better.

Here's the code to extract the zip. what i did is checked for a number in the string, once i found it i checked
for next 4 character if they are number (as zipcode is 5 digits). if all 5 character are number then i m assiging
it to a variable and coming out of loop otherwise i m going for next charachter.
creat two formulas
1st formula name is  "zipcode" which has following code
     
shared numbervar strlen := length({table.fieldname});
shared stringvar zipcode := "";
shared numbervar i := 0;
shared numbervar a := 0;
shared numbervar b := 0;
shared numbervar c := 0;
shared numbervar z  := 0;
for i := 1 to strlen do
if mid({table.fieldname},i,1) in ["0","1","2","3","4","5","6","7","8","9"] then
   (
     a := i;
     b := i;
     c :=  i + 4;
     z  := 0;
     zipcode := "";
    for a := b to c do
    ( if mid({table.fieldname},a,1) in ["0","1","2","3","4","5","6","7","8","9"] then
         (z := z + 1;
          zipcode := zipcode &mid({table.fieldname},a,1);
          if z = 5 then
          i := i+strlen+1;
          zipcode)
     else
           zipcode;
    )
    
   )
 
 
2. the 2nd formula is  "zip" which has folllowing code
     shared stringvar zipcode;
 
now keep  both  the formula in the report and supress zipcode.
You will get the zipcode

As I said this is not mine, I took it from somewhere else however the way he did it, makes more sense.

Let us know if this works.

Regards

Jehanzeb

[Former Member]

Hi Tracy,

Try this :

stringVar PCode := {Customer.Customer Name};

numberVar i:=1;

numberVar Strlen := length (PCode);

numberVar Result:= -1;

while i <= Strlen and Result= -1 do

(

stringVar Position:= Mid (PCode,i,1);

if Position <> " " then

Result:= i;

i:= i + 1;

);

stringVar PostSearch := mid (PCode, Result, Result);

PostSearch

Thanks,

Sastry