Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
update a string |
Thu, Dec 10 2009 10:15 AM | Permanent Link |
"Rita" | 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 |
Thu, Dec 10 2009 10:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Thu, Dec 10 2009 11:05 AM | Permanent Link |
"Rita" | 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] |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |