Postcodes 1

[karengarrette]

Hi

I want to split a postcode field in inpromptu, the postcodes can be anything from 5 to 8 characters in lenght with no fixed format in terms of letter number number letter letter etc. I want to seperate them up so that I have the first part ie, PR, or M, then the number which follows, 23, 3

So a postcode which is PR2 4RG, would be spli into PR, then PR2, but M1 5JG, would split, M then M15. So i want to know if there is a way of splitting a string into numbers and letters, I have tried Char, pad, etc whithout success.

Hope someone can help

Thanks

 

[griffindm]

You can test to see whether a character has an integer equivilant and use the result to parse the string. An example is:

left(<str_exp>,if(string-to-integer(substr(<str_exp>,2,1) = 0) then (2) else ( ... repeat for however many iterations you need for string size)...

This gives you the left side of the string to the first integer equivalent. You can use a similar technique to get the right side.

Complicated, but if you really need it. It's also local processing, so don't use the result in a filter.

HTH,

Dave Griffin ;-)

 

[quatermass]

Hi,

You could also try the following, which works with good old British postcodes:

substring (postcode field, 1, if (substring(postcode field, 2, 1) between '0' and '9') then (1) else (2))

This has the advantage that it can be used in database only mode.

Hope it is of some help

Kevin **************************************************************
The difference between fiction and reality is that fiction has to make sense.
**************************************************************

 

[griffindm]

Good one Kevin,

Always look for ways to eliminate local processing.

You would have to have Automatic Oracle Decode ON for this to work in Oracle though, unless you used a series of Decodes manually to test for 0 - 9 seperately.

Thanks,

Dave Griffin ;-)

 

[karengarrette]

Thanks to you both, just what I was looking for