MySQL regexp Replace

MySQL provides you with a useful string function called REPLACE that allows you to replace a string in a column of a table by a new string. The REPLACE function is very handy to search and replace text which affects multiple records such as obsolete URL, spelling mistake and such.

The syntax of REPLACE function is as follows:

UPDATE tbl_name
SET field_name = REPLACE(field_name, string_to_find, string_to_replace)
WHERE conditions;

Note that when searching for text to replace, MySQL uses case-sensitive match to perform search for string to be replaced.

For example, if you want to correct the spelling mistake in the products table in the sample database, you use the REPLACE function as follows:

UPDATE products
SET productDescription = REPLACE(productDescription,'abuot','about');

The query will look at the column productDescription of the table products and find for all occurrences of spelling mistake ‘abuot’ and replace it by the correct word ‘about’.

It is very important to note that in the REPLACE function, the first parameter is the field name without quotes. If you put the quotes to the field name like ‘field_name’, the query will update the content of that column to ‘field_name’, which can cause some unexpected data loss.

Tags:

Last rowcount in MySQL

#mysql

Suppose you want to count the number of lines returned by the last SQL statement issued. For **select** statements you can use the *FOUND_ROWS* construct: ```sql SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS(); ``` This will return the number of rows in the last **select** query (or if the first query ... Read more

20 Sep 2016 - less than 1 minute read

MySQL punch user creation DDL

#mysql

You can get the DDL necessary to duplicate an existing user with the following system command: ``` MYSQL_CONN="-uroot -ppassword" mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user'' " | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants... Read more

20 Jul 2016 - less than 1 minute read

InnoDB table rebuild

#mysql

### PROBLEM On a **MySQL 5.6** database server, the *ibdata1* file includes 5 InnoDB tables in the mysql schema. ```sql mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table... Read more

20 Jun 2016 - 1 minute read