UK postcodes 7 characters long
with or without a space some
look like  AA1 2BB  7 CHAR
with a space
others
look like   AA112BB

How can I insert a space before the
2 in the 2nd example 1,600,000 + records
to update.
So if the PCODE field contains a space
leave that record alone but insert a space
before the 5th character so it looks like
AA11 2BB
Rita

Rita

Along the lines of

UPDATE table
SET POSTCODE = SUBSTR(POSTCODE,1,3)+' '+SUBSTR(PostCode,4,4)
WHERE
LENGTH(PostCode) > 3 **
SUBSTR(PostCode,3,1) IN ('0','1','2','3','4','5','6','7','8','9')
AND
SUBSTR(PostCode,4,1) <> ' '

** You need to allow for those pesky London postcodes N1 etc and I'm not 100% sure what the pattern should be. I do know there are some inconsistencies.

Roy Lambert [Team Elevate]

Thanks Roy i sorted it by
copying the ones with a space
to a text file then deleting them then
update rtukindex
set postcode = left(postcode for 4) + '  ' + right(postcode for 3)
then imported the text file

Rita

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:1ED72D09-4950-4A7B-A3C6-3E4C6EE366D9@news.elevatesoft.com...
> Rita
>
> Along the lines of
>
> UPDATE table
> SET POSTCODE = SUBSTR(POSTCODE,1,3)+' '+SUBSTR(PostCode,4,4)
> WHERE
> LENGTH(PostCode) > 3 **
> SUBSTR(PostCode,3,1) IN ('0','1','2','3','4','5','6','7','8','9')
> AND
> SUBSTR(PostCode,4,1) <> ' '
>
> ** You need to allow for those pesky London postcodes N1 etc and I'm not
> 100% sure what the pattern should be. I do know there are some
> inconsistencies.
>
> Roy Lambert [Team Elevate]