Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

Common string operations are as follows:

function instructions
replace(str, a,b) Replace a with b in the string STR
concat(str1, str2, … , strn) The str1, str2,… , STRN is used to merge into a complete string
concat_ws(s, str1, str2, … , strn) The str1, str2,… , STRN is merged into a complete string using the hyphen s
left(str, n) Gets the leftmost n characters of the string STR
right(str, n) Gets the n characters to the right of the string STR
substring(str, m, n) Gets n characters of length beginning at position m of the string STR
ltrim(str) Removes the space to the left of the string STR
rtrim(str) Removes the space to the right of the string STR
trim(str) Removes Spaces at the beginning and end of the string STR
char_length(str) Returns the length of the string STR
length(str) Returns the length of the string STR in bytes
repeat(str, n) Repeat the string STR n times

1 String Substitution

SELECT
uid,
REPLACE(uid,"E", "e") as replace_id
FROM
chapter7
Copy the code

result:

2 String Merging

2.1 Merge the default concatenation

SELECT
uid,
name,
CONCAT(uid, name) as id_name
FROM
chapter7
Copy the code

result:

2.2 User-defined concatenation

SELECT
uid,
name,
CONCAT_WS("-",uid, name) as id_name
FROM
chapter7
Copy the code

result:

3 String interception

3.1 Intercept the left part

SELECT LEFT("2019-10-01 12:20:23",10)
Copy the code

result:

3.2 Intercept the right part

SELECT RIGHT("2019-10-01 12:20:23",8)
Copy the code

result:

3.3 Intercept the middle part

SELECT SUBSTRING("2019-10-01 12:20:23",6, 2)
Copy the code

result:

4 String Matching

4.1 Matching Characters of Any Length

SELECT * FROM chapter7 WHERE 'name' LIKE "%"Copy the code

result:

Here are the records that match the name column starting with zhang

SELECT * FROM chapter7 WHERE 'name' LIKE "% li %"Copy the code

result:

Here is the record that matches the name column that contains the sheet (the sheet can not start with the sheet)

4.2 Matching a Single Character

SELECT * FROM chapter7 WHERE 'name' LIKE 'li _'Copy the code

result:

5 String Counting

5.1 Obtaining the Number of Characters in a String

SELECT CHAR_LENGTH("sql")
Copy the code

result:

SELECT CHAR_LENGTH(" delete library run ")Copy the code

result:

5.2 Obtaining the number of bytes of a string

SELECT LENGTH("sql")
Copy the code

result:

SELECT * from * where * SELECT * from * where *Copy the code

result:

It can be found that the byte length and the character length are the same when the string is in English, but the difference will occur when the string contains Chinese characters due to encoding problems

6 Remove string Spaces

SELECT
LENGTH(" abcdef ") as str_length,
LENGTH(LTRIM(" abcdef ")) as lstr_length,
LENGTH(RTRIM(" abcdef ")) as rstr_length,
LENGTH(TRIM(" abcdef ")) as tstr_length
Copy the code

7 String Repetition

SELECT REPEAT("sql", 3)
Copy the code

result: