SpitFire Help Desk

Category » 12. MySQL Queries

There were 40 articles found in this category:

1. How to Use MySQL Control Center
   You can run specific queries in MySQL Control Center to look up information that may not be accessible through Listmaker Pro. Open the MySQL CC by double-clicking on the icon on the desktop. You will see the various lists that you can double-click on to get more information. Once you have doub ...
2. Remove 1 from 11digit numbers
   Update clients set voicephone=substring(voicephone,2,10) where length(voicephone)=11 and substring(voicephone,1,1)='1' Note: This query will fail if the list contains duplicate phone numbers. It will stop at the point it finds a duplicate. You will have to run the query repeatedly to continue fi ...
3. Connect Time by Date
   This can be used to view only the LineTime for only those calls that connected to a voice, answering machine or fax. So this would exclude any no answers, busies and operator intercepts. Note that the name of the list is 214 Also, the SpitFire LineTime is an internal calculation that starts at t ...
4. Line Time Query
   This MySQL query can be used to look up the SpitFire software's LineTime value from the Calls table of a list. Note that 214 is the name of the list and the time stamp is for all calls made on 3/25/08. Also, the SpitFire LineTime is an internal calculation that starts at the moment one of the Di ...
5. Changing Dispositions
   update clients set lastresult='Hit' , dialresult='201' , Hit='Yes' where voicephone='2148878352'
6. Exporting Nocalls to text
   Select voicephone into outfile 'c:\\nocalls.txt' fields terminated by ',' lines terminated by '\r\n' from nocalls limit 100000000
7. Moving alt phone numbers to primary field
   update clients set voicephone=SecVoicePhone where length(voicephone)=0
8. access queries
   update clients set lastresult='Answering Machine' and dialresult='202' where lastresult='none' and dialresult='01' and clientid75001 and zipcode
9. client ID range
   delete FROM `clients` where clientid > 9329 and clientid < 14986 . This will delete the records from clientid 9330 to 14985. It won't delete records with clientid 9329 and 14986.
10. concatenate columns
    here's the query for concatinating the lastname and firstname in name field: update clients set name = concat(firstname, ' ', lastname) (or) update clients set name = concat(lastname, ' ', firstname) (or Where data has been added to a existing list) update clients set name=concat(firstname,' ',l ...
11. delete like
    This query can be modified to delete any range with like parameters delete from clients where city='escondido'
12. delete like range
    Delete from clients where zipcode like '92122%'
13. delete over 10
    delete FROM `clients` where length(voicephone) > 10;
14. Delete specific
    delete from clients where city='escondido'
15. Delete Specific Area Code
    delete from clients where Voicephone like '801%'
16. delete voicephone range
    delete from clients where voicephone>'lower limit' and voicephone
17. Deleting Like
    delete from Clients WHERE voicephone like '817%'
18. Deleting records with a certain prefix
    If the phone number is like the format 9725551234 then the query would be: Delete from clients where substring(voicephone,4,3)='555' If the phone number format is 972-555-1234 then the query would be: Delete from clients where substring(voicephone,5,3)='555'
19. Dial by Street Name
    and address like '%Dallas Pkwy' and (address like '%Dallas Pkwy' or address like '%commonwealth dr')
20. Dial By Zipcode
    Custom query for dialing by zip code: Use the % symbol for zip codes which could be 5 digit or 10 digit Use = for exact parameters. And (Zipcode like ’75001%’ or Zipcode like ’75254%’ or Zipcode like ‘75205%’)