There is currently no built-in function to do this, so I use: SELECT LENGTH( ‘THIS TEST’ ) – LENGTH( REPLACE (‘THIS TEST’,’T’,”); Answer output is: 3
How to show records for Last Month, This Month, Last Week, This Week, Last Year, This Year in MySQL
Just add these AND SQL statements to your queries, in these examples the ORDER_DATE is your column name which holds the date you are comparing against. This Week AND YEARWEEK(ORDER_DATE) = YEARWEEK(CURRENT_DATE) Last Week AND YEARWEEK(ORDER_DATE) = YEARWEEK(CURRENT_DATE – INTERVAL
How to find all the duplicates in my MySQL table
SELECT <column name>, COUNT( <column name> ) AS numOcc FROM <table> GROUP BY <column name> HAVING ( COUNT( <column name>) >1 ) This is an example of where you have duplicates in one column only.
How to delete a primary key in MySQL
ALTER IGNORE TABLE <table> DROP PRIMARY KEY
How to add a primary key index to a table and ignore and remove duplicates
Use the following SQL statement, put more than one column name in the brackets if you want more than one column as the primary key. ALTER IGNORE TABLE ADD PRIMARY KEY ()
How to get the first word of a string using MySQL
There are two ways to do this using MID and LOCATE or by just using SUBSTRING_INDEX a. SELECT MID(,1,LOCATE(‘ ‘,)) FROM b. SELECT SUBSTRING_INDEX( , ‘ ‘, 1 ) FROM
How can I capitalise each word in a MySQL text string without using PHP
This function below will capitalise each word in the string, in the same way that UCWORDS works in PHP. Just copy this function code and run it at the Mysql command line prompt. DELIMITER $$ DROP FUNCTION IF EXISTS `ICap`$$
How to set text to have a Capital letter and then lowercase letters
In PHP we would use UCFIRST, so how do we do this in MySQL? SELECT CONCAT(UPPER(SUBSTRING(firstName, 1, 1)), LOWER(SUBSTRING(firstName FROM 2))) AS properFirstName You could also create your own MySQL function of this and then just call it, instead of
How to display database records which are so many days old
Have a date field in your table which includes the date the record was inserted, try a TIMESTAMP data type, for our example our field is called ‘DATE_ADDED’ and then use the DATEDIFF MySQL function. Then run the following type
How to select a random set of records using MySQL
ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows: SELECT * FROM <tablename> ORDER BY RAND() LIMIT 1000; Note that RAND() in a WHERE clause is re-evaluated every time the WHERE