Update phone numbers in MySQL

We have a bug which stores international UK phone numbers with trunk prefix in the database. For instance, the UK number +442079978240 is wrongly stored as 4402079978240. The request is to update these numbers to remove trunk prefix 0 from the database. For above example, update 4402079978240 to 442079978240 removing the first zero.

The thought is to use regexp to do this update, and Mysql does provide regexp operator and regexp_replace, regexp_substr etc function. However, our current db engine doesn't support regexp_xxx series functions, so we have to rely on regexp operator and other avaliable functions to complete this task.

select out matched numbers using regexp
select phone_number from phone_number_table where phone_number REGEXP '^(440)';

preview result
select concat('44',  right(phone_number, CHAR_LENGTH(phone_number) - 3)) from phone_number_table where phone_number REGEXP '^(440)';

update numbers
UPDATE phone_number_table SET phone_number=concat('44', right(phone_number, CHAR_LENGTH(phone_number) - 3)) WHERE phone_number REGEXP '^(440)';

However, as the where clause doesn't have KEY column, so above update SQL will get an error
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

disable safe mode, then preform update
SET SQL_SAFE_UPDATES=0;

cleanup
enable safe mode and verify the result
SET SQL_SAFE_UPDATES=1;
select phone_number from phone_number_table where phone_number REGEXP '^(440)';

Regexp Lookahead vs Lookbehind

http://www.regular-expressions.info/lookaround.html

Lookahead means look forwards (to the right)
If the regex is x(?=insert_regex_here), which is positive look ahead, that means "find an x that is followed by insert_regex_here".

If the regex is x(?!insert_regex_here), which is negative look ahead, that means "find an x that is not followed by insert_regex_here".

Lookbehind means look backwards (to the left)
If the regex is (?<=insert_regex_here)x, which is positive look behind, that means "find an x that is preceded by insert_regex_here".

If the regex is (?<!insert_regex_here)x, which is negative look behind, that means "find an x not preceded by insert_regex_here"

The above (?=   (?!   (?<=   (?<! etc are Perl regex syntax - the syntax might be slightly different depending on your flavour of regex. You can also use https://regex101.com/ to play around these two concepts in RegExp.

Email address

Email address has two parts, namely local-part and domain part, usually in  local-part "@" domain format.

The local-part of the e-mail address may use any of these ASCII characters:

• Uppercase and lowercase English letters (a-z, A-Z)
• Digits 0 to 9
• Characters ! # $ % & ' * + - / = ? ^ _ ` { | } ~
• Character . (dot, period, full stop) provided that it is not the first or last character, and provided also that it does not appear two or more times consecutively.

The domain part is defined as follows:

The Internet standards (Request for Comments) for protocols mandate that component hostname labels may contain only the ASCII letters a through z (in a case-insensitive manner), the digits 0 through 9, and the hyphen (-). The original specification of hostnames in RFC 952, mandated that labels could not start with a digit or with a hyphen, and must not end with a hyphen. However, a subsequent specification (RFC 1123) permitted hostname labels to start with digits. No other symbols, punctuation characters, or blank spaces are permitted.

The format of email addresses is local-part@domain where the local-part may be up to 64 characters long and the domain name may have a maximum of 253 characters – but the maximum of 256-character length of a forward or reverse path restricts the entire email address to be no more than 254 characters long.

One Javascript Example:
/^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$/

Backbone validation Example:
email: /^((([a-z]|\d|[!#\$%&'\*\+\-\/=\?\^_`{\|}~]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])+(\.([a-z]|\d|[!#\$%&'\*\+\-\/=\?\^_`{\|}~]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])+)*)|((\x22)((((\x20|\x09)*(\x0d\x0a))?(\x20|\x09)+)?(([\x01-\x08\x0b\x0c\x0e-\x1f\x7f]|\x21|[\x23-\x5b]|[\x5d-\x7e]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(\\([\x01-\x09\x0b\x0c\x0d-\x7f]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF]))))*(((\x20|\x09)*(\x0d\x0a))?(\x20|\x09)+)?(\x22)))@((([a-z]|\d|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(([a-z]|\d|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])([a-z]|\d|-|\.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])*([a-z]|\d|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])))\.)+(([a-z]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(([a-z]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])([a-z]|\d|-|\.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])*([a-z]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])))$/i,

For more info, check out the following links:
http://en.wikipedia.org/wiki/Email_address
http://stackoverflow.com/questions/2049502/what-characters-are-allowed-in-email-address

Javascript replace all line breaks in a string with

1. str = str.replace('\n', '<br />');  -> However, this only replace the first ocurrence
2. str = str.replace(/\n/g, '<br />'); -> use regexp to do global match
3. str.replace(new RegExp('\n','g'), '<br />') -> similar to #2, but use constructor to build RegExp
4. str = str.split("\n").join("<br />"); -> split and join, another way to do replace
5. String.prototype.replaceAll = function(needle, replacement) {return this.split(needle).join(replacement||"");}; -> similar to #4, but added to String prototype

Btw, probably we need take care of \r\n case.