Ignore text after a special character

[#1]

Hi everyone,

I have a string which is a persons address, often this ends with a postcode ....AB11AB, or something the postcode will be AB121AB. I used an expression found on here to extract the postcode if it is the last part of the string:

REPLACE(

     TRIM

     (right(PatientFullAddress,7)  &

      subfield

       (subfield

        (PatientFullAddress,right

         (PatientFullAddress,7)

        ,-1)

       ,' ','')

      )

     ,' ','') as PatientPostcode2,

  left(subfield(Postcode,' ',1),len(subfield(Postcode,' ',1)))

But I have found the address string sometimes has AB11AB : house number (Insert a number here for this example).

How can I use the above but exclude all of the text after the ':', or is there a way to improve what I have already?

Many thanks

[matt_crowther]

To isolate everything prior to the ':' I'd look to use something in the load script like:

left(MyValues,index(MyValues,':')-2) as MyVlaues

That assumes you have a space between the end of the PostCode and the ':'.

Obviously replace 'MyValues' with your text string.

Hope that helps,

Matt - Visual Analytics Ltd

Qlikview Design Blog: http://QVDesign.wordpress.com

@QlikviewBI

[#3]

Hi Matt, thanks for teh reply. I could only get this to work on those records where the address contains ':'. I would ike it to include the other entries as well e.g.

anystreet AB11YZ: 9876

anyplace AB114NB

the above would turn out: -

AB11YZ

AB114NB

Many thanks