Several common usages of MySQL string processing function

1. String case conversion:

(1). Replace all lowercase letters in the user_name field of the tbl_student table with uppercase letters:

Update TBL _ studentsetuser _ name = upper (user _ name);

(2) replace all capital letters in the user_name field of the tbl_student table with lowercase letters:

Update TBL _ studentsetuser _ name = lower (user _ name);

2. Clear leading and trailing spaces of the string, or specify characters:

(1). Clear the leading and trailing spaces in the user_name field in the tbl_student table.

update TBL _ student set user _ name = TRIM(' from user _ name)；

(2). Clear the specified character a in the string header:

Update TBL _ studentset user _ name = trim (starting with user _ name and starting with' a');

(3). Clear the specified character b at the end of the string.

Update TBL _ studentset user _ name = trim (trailing' B ')； from user _ name);

3. Replace the string:

(1). Replace the number 1 in the string with the letter I.

update TBL _ student set user _ name = replace(user _ name，' 1 '，' I ')；

4. Use regular expressions:

(1). If the last two are letters, remove them.

Update TBL _ stustu.user _ name = substring (stu.user _ name, LENGTH(stu.user_name)-2) where stu.user_name REGEXP '[a-zA-Z]{2,} $';

Several common usages of MySQL string processing function

Label: