mtm81


Hi,

I have two tables.. one has a list of the UK postcodes.. well..it's actually the districts..

so it's the first two three or four characters (basically all UK postcodes are one or two letters.. then one or two numbers.. then a gap.. and then some more info)

So my first table has everything "before the gap" in seperate records.

My second table is full of exact postcodes.. so everything before and after the gap.

What I need to do is.. for each "full" postcode I have... strip it down so I get it formatted to everything before the gap.. and then find out how many of those I have...

This is also complicated because many of the "full postcodes" don't have the gap.

so if I have:

er56 7tr
fr2 4tg
er567tr
po3 8ty

I would want results of:

er56 - 2
fr2 - 1
po3 - 1

One I have that, I can bring in the second table with all of the parcial postcodes to do an OUTER JOIN and get the postcodes where I DONT have any results.

I know that's pretty complicated, so I'm stuggling a little!

sO i need to get the first "X" amount of characters out of a db value (it could be two values.. could be up to four- first one or two will be char, second one or two will be numeric), then do a count.

Can anyone help




Re: OUTER JOIN plus split? - how to?

mtm81


Just to help clarify:

If I was doing this ASP I would write it as:

get first value from string
- add to output string


get second value from string
- add to output string

get third value
If numeric - add to string
If not - stop loop

get fourth value
If numeric - add to string

If not - stop loop






Re: OUTER JOIN plus split? - how to?

mtm81

ok thought of an easier way - but still need help.

In my SELECT clause, need to have a variable which is gathered by the length of the postcode string.. so:

1) - Remove any spaces in string
2) - Check length
3) - If length = 5 then postcode = first 2 chars in string
4) - If length = 6 then postcode = first 3 chars in string
5) If length > 6 then postcode = first 4 chars in string.

Any ideas






Re: OUTER JOIN plus split? - how to?

mtm81

I have solved this, but have another issue which has surfaced - other post...