I have a field of town names with the uk postcode outings
column under the town name. How can I make it look like

Shepton Mallet,BA4
Wells,BA5

And so on from below

Shepton Mallet
BA4
Wells
BA5
Glastonbury
BA6
Castle Cary
BA7
Templecombe
BA8
Wincanton
BA9
Bruton
BA10
Frome
BA11
Warminster
BA12
Westbury
BA13
Trowbridge
BA14
Bradford on Avon
BA15
Street
BA16
Yeovil
BA20

All ideas welcome.
Rita

All done thanks added dummy field and ran this SQL but if u know
an easier way to write this such as ['0..9'] would be nice.

update test1
SET dummy = '@'
WHERE (town LIKE '%0') OR (town LIKE '%1') OR (town LIKE '%2') OR (town LIKE
'%3') OR (town LIKE '%4')
OR (town LIKE '%5') OR (town LIKE '%6') OR (town LIKE '%7') OR (town LIKE
'%8') OR (town LIKE '%9')

Rita


Unless you have some towns with very short names how about LENGTH(Town) <= 4

Roy Lambert

Rita

> I have a field of town names with the uk postcode outings
> column under the town name. How can I make it look like
>
> Shepton Mallet,BA4
> Wells,BA5
>
> And so on from below
>
> Shepton Mallet
> BA4
> Wells
> BA5

As long as the table has  not had rows deleted and it always has town then
postcode the following should do it

select t1.col,t2.col,t1.recordid as first from table t1
join table t2 on t2.recordid=t1.recordid+1
group by recordid
having t1.recordid mod 2 = 1


John

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:23EE5520-831B-4D23-BBD2-DC525A024A49@news.elevatesoft.com...
> Rita
>
>
> Unless you have some towns with very short names how about LENGTH(Town) <=
> 4
>

That would be ok but 1,000's of Ely Ayr and so on plus some pcodes in
the outings have upto six and down to 2 for Jersey its a pain.
Its never easy I have not wrestled with SQL for around 2 years now
its an age thing I guess. I have hand edited a 5250 + line csv today tho
from 2:00 pm until now 6:45 not bad for an old girl eh!
I will try John's idea tho.
Rita

looks good to me John of to try it after a coffee thanks.
Rita

>
> select t1.col,t2.col,t1.recordid as first from table t1
> join table t2 on t2.recordid=t1.recordid+1
> group by recordid
> having t1.recordid mod 2 = 1
>

"Rita" wrote:

looks good to me John of to try it after a coffee thanks.
Rita

>
> select t1.col,t2.col,t1.recordid as first from table t1
> join table t2 on t2.recordid=t1.recordid+1
> group by recordid
> having t1.recordid mod 2 = 1
>

Rita
Hi Using Ultra edit:

Replace ^p with ','

replace ',B' with ' B'

replace ','  with ',^p'

Allan

Allan

> looks good to me John of to try it after a coffee thanks.
> Rita
>
> >
> > select t1.col,t2.col,t1.recordid as first from table t1
> > join table t2 on t2.recordid=t1.recordid+1
> > group by recordid
> > having t1.recordid mod 2 = 1
> >
>
> Rita
> Hi Using Ultra edit:
>
> Replace ^p with ','
>
> replace ',B' with ' B'
>
> replace ','  with ',^p'
>
> Allan
>

Is this some sort of secret code or am I just being dumb ?

John

John


>Is this some sort of secret code or am I just being dumb ?

If you are then so am I. Can't make head or tail of it.

Roy Lambert

"Allan Brocklehurst" <brock@ns.sympatico.ca> wrote in message
news:C276C62E-A814-417F-A1B7-849014CC34D8@news.elevatesoft.com...
> Hi Using Ultra edit:
>
> Replace ^p with ','
>
> replace ',B' with ' B'
>
> replace ','  with ',^p'
>

Cool that makes it easy peasy thanks for that.

Rita