MySQL字符串函數(shù)

1.concat

將兩個或多個字符串組合成一個字符串

MySQL CONCAT()函數(shù)需要一個或多個字符串參數(shù)，并將它們連接成一個字符串。CONCAT()函數(shù)需要至少一個參數(shù)，否則會引起錯誤

連接之前將所有參數(shù)轉(zhuǎn)換為字符串類型。如果任何參數(shù)為NULL，則CONCAT()函數(shù)返回NULL值。

如果想用連接符，需要在連接的項目之間拼接上連接符。

查詢語句：select CONCAT(supplier_id,'-',supplier_name) from bms_bills_memo where id = 4
結(jié)果：6-供應(yīng)商4

CONCAT_WS()函數(shù)將兩個或多個字符串值與預(yù)定義的分隔符相連接。

第一個參數(shù)是分隔符，后面是要拼接的參數(shù)

只有分隔符為null時才返回null，如果項目為null時，則不拼接

查詢語句：
select CONCAT_WS('-',supplier_id,supplier_name) from bms_bills_memo where id = 4

select CONCAT_WS('-',null,supplier_name) from bms_bills_memo where id = 4

select CONCAT_WS('-',null,null) from bms_bills_memo where id = 4
結(jié)果：
6-供應(yīng)商4
供應(yīng)商4
空

2.length和char_length函數(shù)

以字節(jié)和字符獲取字符串的長度

• length：獲取以字節(jié)為單位的字符串長度
• char_length：獲取以字符為單位的字符串的長度

字節(jié)(Byte)是計量單位，表示數(shù)據(jù)量多少，是計算機(jī)信息技術(shù)用于計量存儲容量的一種計量單位，通常情況下一字節(jié)等于八位。

字符(Character)計算機(jī)中使用的字母、數(shù)字、字和符號，比如'A'、'B'、'$'、'&'等。

一般在英文狀態(tài)下一個字母或字符占用一個字節(jié)，一個漢字用兩個字節(jié)表示。

• ASCII 碼中，一個英文字母（不分大小寫）為一個字節(jié)，一個中文漢字為兩個字節(jié)。
• UTF-8 編碼中，一個英文字為一個字節(jié)，一個中文為三個字節(jié)。
• Unicode 編碼中，一個英文為一個字節(jié)，一個中文為兩個字節(jié)。
• 符號：英文標(biāo)點為一個字節(jié)，中文標(biāo)點為兩個字節(jié)。例如：英文句號 . 占1個字節(jié)的大小，中文句號 。占2個字節(jié)的大小。
• UTF-16 編碼中，一個英文字母字符或一個漢字字符存儲都需要 2 個字節(jié)（Unicode 擴(kuò)展區(qū)的一些漢字存儲需要 4 個字節(jié)）。
• UTF-32 編碼中，世界上任何字符的存儲都需要 4 個字節(jié)。

查詢語句：
select LENGTH(supplier_name) from bms_bills_memo  where id = 4

select CHAR_LENGTH(supplier_name) from bms_bills_memo  where id = 4
結(jié)果：
10
4
表中數(shù)據(jù)
supplier_name：供應(yīng)商4

結(jié)合使用

SELECT postid,
       title,
       IF(CHAR_LENGTH(excerpt) > 20,
          CONCAT(LEFT(excerpt,20), '...'),
          excerpt) summary
FROM posts;

我們使用IF函數(shù)來檢查摘錄列的長度是否大于20，并使用CONCAT語句將excerpt列的值與省略號(...)連接起來，否則只是獲取全部摘錄(excerpt)內(nèi)容

3.left

返回具有指定長度的字符串的左邊部分

查詢語句：
select LEFT(client_name,4) from bms_bills_memo where id = 4
結(jié)果：
青島自貿(mào)
表中數(shù)據(jù)
client_name：青島自貿(mào)新零售體驗中心有限公司

LEFT()函數(shù)接受兩個參數(shù)：

• str是要提取子字符串的字符串。
• length是一個正整數(shù)，指定將從左邊返回的字符數(shù)。

LEFT()函數(shù)返回str字符串中最左邊的長度字符。如果str或length參數(shù)為NULL，則返回NULL值。

如果length為0或為負(fù)，則LEFT函數(shù)返回一個空字符串。如果length大于str字符串的長度，則LEFT函數(shù)返回整個str字符串。

4.right

回具有指定長度的字符串的右邊部分

查詢語句：
select RIGHT(client_name,4) from bms_bills_memo where id = 4
結(jié)果：
有限公司
表中數(shù)據(jù)：
client_name：青島自貿(mào)新零售體驗中心有限公司

5.replace

搜索并替換字符串中的子字符串。

有三個參數(shù)，它將string中的old_string替換為new_string字符串

請注意，當(dāng)搜索要替換的文本時，MySQL使用區(qū)分大小寫匹配來執(zhí)行要替換的字符串的搜索

不支持正則表達(dá)式

查詢語句：
select REPLACE(client_name,'青島','威海') from bms_bills_memo where id = 4
結(jié)果：
威海自貿(mào)新零售體驗中心有限公司

查詢語句：
select REPLACE(bill_of_lading_no,'td','ab') from bms_bills_memo where id = 4
結(jié)果：
TD10004

結(jié)果沒有發(fā)生變化，因為區(qū)分大小寫

6.substring

從具有特定長度的位置開始提取一個子字符串。

SUBSTRING(string,position);
SUBSTRING(string FROM position);

有兩個參數(shù)：

• string參數(shù)是要提取子字符串的字符串。
• position參數(shù)是一個整數(shù)，用于指定子串的起始字符，position可以是正或負(fù)整數(shù)。

如果position為正，則SUBSTRING函數(shù)從字符串的開始處提取子字符串。

如果position參數(shù)為零，則SUBSTRING函數(shù)返回一個空字符串。

可以使用SQL標(biāo)準(zhǔn)語法與FROM關(guān)鍵字一起調(diào)用SUBSTRING函數(shù)

查詢語句:
select SUBSTRING(bill_of_lading_no,2) from bms_bills_memo where id = 4
結(jié)果：
D10004

查詢語句：
select SUBSTRING(bill_of_lading_no,-2) from bms_bills_memo where id = 4
結(jié)果：
04

表中數(shù)據(jù)：
bill_of_lading_no:TD10004

如果要指定要從字符串中提取的子字符串的長度，可以使用以下形式的SUBSTRING函數(shù)

SUBSTRING(string,position,length);
SUBSTRING(string FROM position FOR length);

查詢語句：
select SUBSTRING(bill_of_lading_no,2,5) from bms_bills_memo where id = 4
結(jié)果：
D1000

查詢語句：
select SUBSTRING(bill_of_lading_no,-7,5) from bms_bills_memo where id = 4
結(jié)果：
TD100

表中數(shù)據(jù)：bill_of_lading_no:TD10004

SUBSTR()函數(shù)是SUBSTRING()函數(shù)的同義詞，所以可以互換使用它們。

SUBSTRING_INDEX的用法

按照某個特定符號進(jìn)行分割，并取出對應(yīng)的值

查詢語句：
select SUBSTRING_INDEX('qwe-etr-tyu','-',1)
select SUBSTRING_INDEX('qwe-etr-tyu','-',2)
select SUBSTRING_INDEX('qwe-etr-tyu','-',-1)
select SUBSTRING_INDEX('qwe-etr-tyu','-',-2)

結(jié)果：
qwe
qwe-etr
tyu
etr-tyu

7.trim

從字符串中刪除不需要的字符。

TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str);

您可以使用LEADING，TRAILING或BOTH選項明確指示TRIM()函數(shù)從字符串中刪除前導(dǎo)，尾隨或前導(dǎo)和尾隨的不必要的字符。

如果您沒有指定任何內(nèi)容，TRIM()函數(shù)默認(rèn)使用BOTH選項。

• [removed_str]是要刪除的字符串。默認(rèn)情況下，它是一個空格。這意味著如果不指定特定的字符串，則TRIM()函數(shù)僅刪除空格。
• str是要刪除子字符removed_str的字符串。
• TRIM()函數(shù)返回一個字符串，刪除不需要的字符。

查詢語句：
//從字符串中除去前導(dǎo)和尾隨空格
SELECT TRIM(' MySQL TRIM Function ');
//僅刪除前導(dǎo)空格
SELECT TRIM(LEADING FROM '    MySQL TRIM Function   ');
//僅刪除尾隨空格
SELECT TRIM(TRAILING FROM '    MySQL TRIM Function   ');
//刪除字符串末尾的換行符
-- 方式一
SELECT 
    TRIM(TRAILING '\n' FROM field_name)
FROM table_name;

-- 方式二
SELECT 
    TRIM(TRAILING '\r' FROM field_name)
FROM table_name;

-- 方式三
SELECT 
    TRIM(TRAILING '\r\n' FROM field_name)
FROM table_name;

如果要僅刪除前導(dǎo)或尾隨空格，則可以使用其他字符串函數(shù)：LTRIM和RTRIM

使用LTRIM函數(shù)來刪除字符串的前導(dǎo)空格

SELECT LTRIM(' MySQL LTRIM function');

使用RTRIM()函數(shù)來刪除字符串的尾隨空格

SELECT RTRIM('MySQL RTRIM function ');

8.find_in_set

在逗號分隔的字符串列表中找到一個字符串

FIND_IN_SET(needle,haystack);

FIND_IN_SET()函數(shù)接受兩個參數(shù)：

• 第一個參數(shù)needle是要查找的字符串。
• 第二個參數(shù)haystack是要搜索的逗號分隔的字符串列表。

FIND_IN_SET()函數(shù)根據(jù)參數(shù)的值返回一個整數(shù)或一個NULL值：

• 如果needle或haystack為NULL，則函數(shù)返回NULL值。
• 如果needle不在haystack中，或者haystack是空字符串，則返回零。
• 如果needle在haystack中，則返回一個正整數(shù)。

請注意，如果needle包含逗號(，)，該函數(shù)將無法正常工作。 此外，如果needle是一個常量字符串，而且haystack是一個類型為SET的列，MySQL將使用位算術(shù)優(yōu)化。

查詢語句：
select FIND_IN_SET('red','yellow,red,blue')
結(jié)果：
2

where中可以直接使用find_in_set

SELECT 
    name, belts
FROM
    divisions
WHERE
    FIND_IN_SET('red', belts);

SELECT 
    name, belts
FROM
    divisions
WHERE
    NOT FIND_IN_SET('black', belts);

FIND_IN_SET函數(shù)與IN運算符具有相同的功能

column IN (x, y, z)表達(dá)式與FIND_IN_SET(column, 'x,y,z')相同。

IN運算符可以接受任意數(shù)量的參數(shù)，每個參數(shù)用逗號分隔。 但是，FIND_IN_SET函數(shù)只有兩個參數(shù)。

當(dāng)您希望將值與數(shù)據(jù)庫中的值列表進(jìn)行匹配時，可以使用IN運算符。 并且當(dāng)希望將值與數(shù)據(jù)庫中以逗號分隔存儲的值列表進(jìn)行匹配時，可以使用FIND_IN_SET函數(shù)。

9.format

格式化具有特定區(qū)域設(shè)置的數(shù)字，舍入到小數(shù)位數(shù)。

FOMRAT(N,D,locale);

FORMAT函數(shù)將數(shù)字N格式化為格式，如"#,###,###.##"，舍入到D位小數(shù)。它返回一個值作為字符串。

FORMAT函數(shù)接受三個參數(shù)：

• N是要格式化的數(shù)字。
• D是要四舍五入的小數(shù)位數(shù)。
• locale是一個可選參數(shù)，用于確定千個分隔符和分隔符之間的分組。如果省略locale操作符，MySQL將默認(rèn)使用en_US。

以下鏈接提供MySQL支持的所有區(qū)域名稱：http://dev.mysql.com/doc/refman/5.7/en/locale-support.html

查詢語句：
select format(123456.1258,2)
select format(123456.1248,2)
結(jié)果：
123,456.13
123,456.12

可以組合兩個函數(shù)：FORMAT和CONCAT。 FORMAT函數(shù)將庫存值格式舍入為2位小數(shù)。 并且concat函數(shù)在庫存值字符串的開頭添加人民幣符號(￥)

SELECT 
    productname,
    CONCAT('￥',
            FORMAT(quantityInStock * buyPrice, 2)) stock_value
FROM
    products;

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論