Trim Query - a little different

building reports desktopintelligence
1

Hi,

I have a list of addresses in the format:

12 Charlie Way, Bournemouth, Dorset

How can I design a variable that will only retrieve the County (in this case - Dorset)?

Much appreciated

RobbieL :uk: (BOB member since 2007-08-22)

2

What sort of pattern will you have in your data?

For now, I see two options. You can capture everything after the second comma “,” or you can retrieve everything from the end of the string backwards until you find the first space. Which would fit your requirements better? Or do you have another way to identify the county?

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Everything from the end of the string towards the first space would fit my requirements perfectly!

How would I go about doing that?

RobbieL :uk: (BOB member since 2007-08-22)

4

Try this code.

SUBSTR('12 Charlie Way, Bournemouth, Dorset',INSTR('12 Charlie Way, Bournemouth, Dorset',' ',-1)+1,LENGTH('12 Charlie Way, Bournemouth, Dorset'))

Rakesh_K :india: (BOB member since 2007-12-11)

5

Rakesh, please post your database details along with the code, if you proactively go for a universe level solution, as suggested earlier in some of your other posts.

Jansi :india: (BOB member since 2008-05-12)