Skip to main content

Posts

Showing posts with the label MySQL

Best way to find duplicate records in a table on database

Method 1: The method will display group wise duplicate leads. SELECT     eabhyasa_id, eabhyasa_name, eabhyasa_email, eabhyasa_mobile,stage_name,count(*) no_of_records FROM rns_leads as L     left join rns_stages as S on S.stg_id= E.eabhyasa_stg_id GROUP BY eabhyasa_email, eabhyasa_mobile HAVING count(*) > 1  Method 2: The below method will display all duplicate leads SELECT L.eabhyasa_id, L.eabhyasa_fname, L.eabhyasa_email, L.eabhyasa_mobile,S.stg_name, case when E.eabhyasa_cp_type=0 then "-" when E.eabhyasa_cp_type=1 then "Fssess" when E.eabhyasa_cp_type=2 then "full" when E.eabhyasa_cp_type=3 then "part1" when E.eabhyasa_cp_type=4 then "part2" when E.eabhyasa_cp_type=5 then "part3" when E.eabhyasa_cp_type=6 then "part4" end as Payment_Stage FROM rns_leads L left join gti_stages as S on S.stg_id= E.eabhyasa_stg_id INNER JOIN ( SELECT eabhyasa_email, eabhyasa_mobile,count(*) no_of_records FROM gt

Remove all whitespaces from the entire column using MySQL Query

The below queries will remove all spaces, tabs characters, new line characters first and last space(s) from the table column. For replace all spaces :  UPDATE `table` SET `col_name` = REPLACE(`col_name`, ' ', '') For remove all tabs characters :  UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\t', '' ) For remove all new line characters :  UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\n', '') http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace For remove first and last space(s) of column :  UPDATE `table` SET `col_name` = TRIM(`col_name`) http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

Common MySql Interview Questions and Answers For Experienced/Freshers

Some Common MySql Interview Questions and Answers For Experienced + Freshers Which mostly asked my interviewer during interview session, As you know MySql is highly popular relational database and good compatible with open source languages like PHP. Following list of basic Mysql question and answer surely help developers for getting new jobs. MySql Interview Questions and Answers Question: What is MySQL? MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language, the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility.   Question: Why MySQL is used? MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet   Question: In

How to change a MySQL database’s table prefix

Changing a database table prefix is easy and here’s the simple step-by-step guide! For WordPress installations, it’s essential! How to change a prefix 1. In your text editor, change database_name, old_prefix_ and new_prefix_ to the required values: SET @database = "database_name"; SET @old_prefix = "old_prefix_"; SET @new_prefix = "new_prefix_"; SELECT concat( "RENAME TABLE ", TABLE_NAME, " TO ", replace(TABLE_NAME, @old_prefix, @new_prefix), ';' ) AS "SQL"  FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database; 2. Run the query in cPanel or PHPMyAdmin on your WordPress database 3. The output will be a series of SQL queries that will rename the tables for you 4. Run the output 5. Done!   How to add a prefix    If your database doesn’t have a prefix at all, follow the steps above but use the below query that’s been slightly modified fo

Selecting COUNT from MySQL table by first letter of a column

Method 1: SELECT count(*) total,     SUM(case when trim(last_name) like 'a%' then 1 else 0 end) A,     SUM(case when trim(last_name) like 'b%' then 1 else 0 end) B,     SUM(case when trim(last_name) like 'c%' then 1 else 0 end) C,     SUM(case when trim(last_name) like 'd%' then 1 else 0 end) D,     SUM(case when trim(last_name) like 'e%' then 1 else 0 end) E,     SUM(case when trim(last_name) like 'f%' then 1 else 0 end) F,     SUM(case when trim(last_name) like 'g%' then 1 else 0 end) G,     SUM(case when trim(last_name) like 'h%' then 1 else 0 end) H,     SUM(case when trim(last_name) like 'i%' then 1 else 0 end) I,     SUM(case when trim(last_name) like 'j%' then 1 else 0 end) J,     SUM(case when trim(last_name) like 'k%' then 1 else 0 end) K,     SUM(case when trim(last_name) like 'l%' then 1 else 0 end) L,     SUM(case when trim(last_name) like 'm%' then 1 e

Mysql regexp to allow numbers and '+'

First, use  NOT REGEXP  instead of  !=1 . To allow  +  at the start optionally, use  ^\\+? . Since  +  is a special character in regular expressions, it must be escaped with a backslash (which must be escaped with another backslash). You then need one or more digits ( [0-9]+ ), up to the end of the string  $ . $query="UPDATE table SET phone='NULL' WHERE phone NOT REGEXP '^\+?[0-9]+$'"; This will match anything that doesn't begin optionally with  + , followed by digits only. If you also need to permit hyphens and dots in the phone number, add them into the  []  character class: '^\\+?[0-9.-]+$'