get substring in mysql

get first char of string

SELECT SUBSTRING('ABC', 1, 1)

get last char of string

SELECT SUBSTRING('ABC', -1, 1)

get second char in string

SELECT SUBSTRING('ABC', 2, 1)

get two first chars in string

SELECT SUBSTRING('ABC', 1, 2)

get two last chars in string

SELECT SUBSTRING('ABC', -2, 2)
Advertisements

format number in mysql

format integer with one decimal place

SELECT FORMAT('21',1)

remove zeros from the end of the number

SELECT TRIM(TRAILING '0' FROM '00701000')

remove zeros from the beginning of the number

SELECT TRIM(LEADING '0' FROM '00701000')

remove zeros from the beginning and end of the number

SELECT TRIM(BOTH '0' FROM '00701000')

replace html chars in mysql

resolve import post characters wrong in wordpress

# replace '"' by '"'
UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`, '"', '"') WHERE `post_content` LIKE '%"%'
# replace '&' by '&'
UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`, '&', '&') WHERE `post_content` LIKE '%&%'
# replace '&lt;' by '<'
UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`, '&lt;', '<') WHERE `post_content` LIKE '%&lt;%'
# replace '&gt;' by '>'
UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`, '&gt;', '>') WHERE `post_content` LIKE '%&gt;%'

phpmyadmin with multiple servers

how to configure phpmyadmin with two servers

vi /path/to/phpmyadmin/config.inc.php
/* Servers configuration */
$i = 0;

/* Server: 192.168.0.7 */
$i++;
$cfg['Servers'][$i]['verbose'] = '';
$cfg['Servers'][$i]['host'] = '192.168.0.7';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = '';
$cfg['Servers'][$i]['password'] = '';

/* Server: 192.168.0.8 */
$i++;
$cfg['Servers'][$i]['verbose'] = '';
$cfg['Servers'][$i]['host'] = '192.168.0.8';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = '';
$cfg['Servers'][$i]['password'] = '';

/* End of servers configuration */

$cfg['blowfish_secret'] = '5707870738.70703';
$cfg['DefaultLang'] = 'en';
$cfg['ServerDefault'] = 1;
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';