Ways to Clean-up messy records in sql -

-

i have following sql data:

id              company name        customer            address 1       city                        state  zip   date 0108500         aaa test            mish~sara           newa claims     chtiana                     co     123   06fe0046         0108500         aaa.test            mish~sara           newa claims     chtiana                     co     123   06fe0046         1802600         aaa test company    ban, adj.~gorge     po box 83       moulaurel                      ca      153   09js0025         1210600         aaa test company    biwel~brce          97kehst ve      jacn                        ca     153   04js0190

aaa test, aaa.test , aaa test company considered 1 company.

since data messy i'm thinking either this:

  1. is there way search records in db wherein search company name same name re-name longest name?

in case, aaa test , aaa.test aaa test company.

  1. or there way filter record company name same can have option change it?

if there's no way via sql query, suggestions can clean-up records? there 1 million records in database , it's hard clean manually.

thank in advance.

you use string matching algorithm jaro-winkler. i've written sql version used daily deduplicate people's names have been typed in differently. can take awhile work fuzzy match you're looking for.


Comments

Post a Comment

Popular posts from this blog

SQL php on different pages to Insert (mysqli) -

-
i have piece of php code, works when placed on index page ( index.php ), nothing if put in page, i.e. add.php for example: having code add.php page, tried go to: www.myhost.com/add.php , nothing happen. if copy code index.php , go page www.myhost.com/index.php add row in sql table. why that? <?php $server="mysql.server.com"; $user="randomusr"; $pass="randompwd"; $db="randomdb"; $link=mysqli_connect($server, $user, $pass, $db); $result=mysqli_query($link,"select * `battery` `id`"); $query = "insert battery (id,type,owner,charge,time) values ('1','lily002','maisnow','15','2015-04-22 17:20:28')"; mysqli_query($link, $query); header("location: index.php"); //this 1 comment out when copied index.php ?> l # name type collation attributes null 1 id int(16) no ...
Read more

php - How can an email be returned from Stripe Checkout? -

-
using stripe, want store customer email address email supplied in checkout. unfortunately, posting stripeemail in charge.php file returns null . how can return email checkout can use send receipt? here's form code: <script src="https://checkout.stripe.com/v2/checkout.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.js"></script> <form action="charge.php" method="post"> <input type="hidden" id="amount" name="chargeamount"/> <button data-charge-amount="300000" data-charge-name="name" data-charge-description="description">select pledge level</button> <button data-charge-amount="123123" data-charge-name="name2" data-charge-description="description2">donate</button> </form> <script> $('button').click(function(){ var token...
Read more

sql - Partition elimination in Greenplum -

-
i have scenario this: select * package package_type in ('box','card') the table partitioned package_type field. assume there twenty possible values package_type field. there twenty partitions including box , card , default partitions. when above query run, partition elimination happens correctly , box , card partitions scanned. result quick. however, when same query written this: select * package package_type in (select package_type package_list_table) , column package_type in package_list_table contains 2 values box , card . when above query run, 20 partitions being scanned. degrades performance. it seems compiler failing identify second query correctly , result partitions getting accessed. any workarounds overcome this? thanks in advance. the postgres manual page on partitioning includes caveat constraint exclusion works when query's clause contains constants (or externally supplied parameters). example, comparison agains...
Read more