Exporting Error and Others
SELECT voicephone, dateedited, dateentered into outfile 'C:/Errors.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM `clients` where dialresult='207' or dialresult='206' or dialresult='249' limit 10000000
- Exporting Nocalls
Select voicephone into outfile 'c:\\nocalls.txt' fields terminated by ',' lines terminated by '\r\n' from nocalls limit 100000000
- Exporting Sorted by Zipcode
SELECT name, company,address, city, state, zipcode, voicephone into outfile 'c:\\770byzip.txt' fields terminated by ',' lines terminated by '\r\n' FROM `clients` order by zipcode limit 10000000
- Insrtuctons for MySQL Sorting
For sorting the Touch Tone 9 into a text file that saves in Folder "Touch Tone 9" on the C drive: SELECT voicephone into outfile 'C:/Touch Tone 9/temp.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM `clients` where spare16 like '9%' limit 1000000 For sorting the Touch Tone 8 into a ...
- Lookup Transfer export-Alternate
SELECT company, address, city, state, voicephone, lastresult into outfile 'c:\\transfers.txt' fields terminated by ',' lines terminated by '\r\n' FROM `clients` where dateedited between '20061218000000' and '20061218235959' and spare16 like '9%' limit 1000000
- Random export
SELECT voicephone into outfile 'c:\\random.txt' fields terminated by ',' lines terminated by '\r\n' FROM clients order by rand() limit 10000000;
- randomizer
SELECT * into outfile 'c:\\random.txt' fields terminated by ',' lines terminated by '\r\n' FROM `clients` where dialresult='01' order by rand() limit 1000000
- Remove data from a column
update Clients set faxphone=''
- Removing punctuation from voicenumbers
1. If you want to remove all the hyphens then use: Update clients set voicephone=replace(voicephone,’-‘,’’) 2. If you want to replace comma’s in the voicephone field Update clients set voicephone=replace(voicephone,’,‘,’’) 3. If you want to replace space’s in the voicephone field Update clients ...
- Rework Single Record
update clients set dialresult='01', lastresult='Rework' where Voicephone='1234567890'
- Scrub Tier C
delete clients.* FROM `clients` LEFT JOIN tier.tierab as `a` ON substring(clients.voicephone,1,6)=a.prefix where a.prefix is null
- Search for records within date span
SELECT count(*) FROM `nocalls` where dateentered between '2006-01-01%' and '2007-04-26%' SELECT * FROM `nocalls` where dateentered between '2006-01-01%' and '2007-04-26%'
- Searching for nocalls
select * from nocalls where voicephone='2143896208'
- Single Record Lookup
select voicephone from clients where voicephone='1234567890'
- Spare1 range outfile
SELECT name, address, city, state, zipcode, email, voicephone, spare1, spare2, spare3, lastname, firstname into outfile 'c:\\Loan Range List.txt' fields terminated by ',' lines terminated by '\r\n' FROM `higher loan limit leads 2`.`clients` where spare1>'375000' and voicephone
- TierC-clean
delete clients.* FROM `clients` LEFT JOIN tier.tierab as `a` ON substring(clients.voicephone,1,6)=a.prefix where a.prefix is null Note: Tier A and B must be combined and imported first as a DB where "tierab" is the DB name.
- Transfers Query
SELECT name, address, city, state, zipcode, voicephone into outfile 'c:\\transfers.txt' fields terminated by ',' lines terminated by '\r\n' FROM `clients` where dateedited between '20080721000000' and '20080721235959' and (spare16 like '1%' or spare16 like '2%') limit 1000000
- TT Queries
For sorting the Touch Tone 9 into a text file that saves in Folder "Touch Tone 9" on the C drive: SELECT voicephone into outfile 'C:/Touch Tone 9/temp.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM `clients` where spare16 like '9%' limit 1000000 For sorting the Touch Tone 8 into a ...
- UK country code removal
update clients set voicephone=substring(voicephone,5,12) where substring(voicephone,1,4)=’+44 ’
- Zipcode Dialing
The first query would be: select * from clients where dialresult='01' and zipcode='75001' the second query would be select * from clients where dialresult='01' and zipcode>75001 and zipcode
