OK, so got some corrupt data in a database, so need to run some sql against it to remove it. I have a Java app that freaks out because of it and cannot be changed.
My address field has somehow got 6 foreign characters in it that are bunched together at various points throughout the database.
Examples
16 New Forest ABCDEF, London
3 Cream FieABCDEFlds, London
27 ABCDEF ommercial Street, Sheffield
ABCDEF Road, Birmingham
ABCDEF, are the the foreign characters, all instances start with the same foreign character (in my example I have used the letter A) but the other five to the right are random, from degree symbols to characters with shading. And the five characters are different in each instance.
Basically I want to select all instances of ABCDEF and replace with 6 underscores or dashes. Please remember the A is always the starting character throughout all the instances of the string I want to replace and the 6 characters appear together at random points within the address field. So I kind of want a script which will search for all occurances that start with A (and the next 5 characters) and replace with 6 underscores.
Apologies, if I have made a pigs ear of explaining this and I will say thank you big time for any help received. Am sure theres an sql expert here?
transpose the data over to a new table, and cut the old data out. Cant remember ths command, but you can be selective which bits you carry over.
Due to nature of work/business; this is not doable. I can only update what is there already.
Suggestions?
You might be able to try a two-pronged approach:
First Run through Removing any Capital Letters that arent at the start of Words (Assuming your addresses are capitalized properly) - use a MATCH CASE statement or whatever the appropriate bobbins is.
Second run through strip all characters that arent in the Ranges A-Z, a-z, 0-9, "," possibly.
If I was you Id want to do one of three things with this, either have a very reliable of the database, take a copy and test offline, or test it all out in a SQL view or something first before actually modifying the data.
Shouldnt be too difficult.
If this was MySQL I would do something like the following.
BEGIN WARNING
Do make a complete backup of the database. I have checked the replace code and it appears to work fine on MySQL. It might be slightly different if you are using MSSQL or Oracle.
END WARNING
First create a new table with the same schema as the old one, then dump all the data in there that needs changing by doing:
INSERT INTO addresses_tmp SELECT * FROM addresses where line1 like %A%
Then review the data in addresses_tmp and make sure there is nothing in there you dont want to update, if there is just delete the rows.
Then we do the update:
UPDATE addresses_tmp SET line1 =
REPLACE(line1,
SUBSTR(line1,
LOCATE(A, line1)
,6)
, ______)
(See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html for details on the functions)
Then once you are happy do a replace into back into the original table:
REPLACE INTO addresses SELECT * FROM addresses_tmp
Job done :)
Hope that helps,
Cornet
Just wanted to say a huge thank you all, especially cornet!
I used cornets script, but had to use INSTR as LOCATE does not work with Oracles limited commands. Have checked all data and also Java app that works in cahoots and all is good.
Thanks all again!!!!