字符串長(zhǎng)度函數(shù)
SELECT CHAR_LENGTH('data'), CHAR_LENGTH('egg');SELECT LENGTH('date');
字符串拼接函數(shù)
- 無(wú)分隔符的拼接
SELECT CONCAT('My SQL', '8.0'), CONCAT('my', NULL, 'SQL');
拼接函數(shù).png - 有分隔符的拼接
SELECT CONCAT_WS('-','lst','2nd','3rd');
分隔符拼接
替代字符串的函數(shù)
SELECT INSERT ('Quest', 1, 4, 'What') AS col2;
替代字符串
字符串大小寫(xiě)
- 小寫(xiě)
SELECT LOWER('BEAUTIFUL'), LCASE('well'); - 大寫(xiě)
SELECT UPPER('black'), UCASE('BLack');
獲取指定長(zhǎng)度的字符串函數(shù)
- 左邊開(kāi)始
SELECT LEFT('football', 5); - 右邊開(kāi)始
SELECT RIGHT('football', 5);
填充字符串函數(shù)
- 左填充
SELECT LPAD('hello', 4, '??'), LPAD('hello',10,'??');
填充字符串 - 右填充
SELECT RPAD('hello', 4, '??'), RPAD('hello',10,'??');
刪除空格函數(shù)
- 左刪除
SELECT '( book )',CONCAT('(',LTRIM(' book '),')');
刪除左空格 - 右函數(shù)
SELECT '( book )',CONCAT('(',RTRIM(' book '),')'); - 中間
SELECT '( book )',CONCAT('(',TRIM(' book '),')');
重復(fù)生成字符串函數(shù)
SELECT REPEAT('mysql',3);
空格函數(shù)
SELECT CONCAT('(', SPACE(6), ')');
字符串替代
SELECT REPLACE('xxx.mysql.com', 'x', 'w');
字符串替代函數(shù)
字符串比較大小
SELECT STRCMP('txt', 'txt2'), STRCMP('txt2', 'txt'), STRCMP('txt','txt');
獲取子串的函數(shù)
- kfast
SELECT SUBSTRING('breakfast',5) AS col1; - bre
SELECT SUBSTRING('breakfast',1,3) AS col1;
SELECT MID('breakfast',1,3) as col1;
匹配字符串位置的函數(shù)
結(jié)果是三個(gè)5
SELECT LOCATE('ball','football'), POSITION('ball' IN 'football'), INSTR('football', 'ball');
字符串逆序
SELECT REVERSE('abc');
返回指定位置的字符串
SELECT ELT(3, '1st', '2nd', '3rd');
