MySQL字符串函數(shù)

1.concat

將兩個(gè)或多個(gè)字符串組合成一個(gè)字符串

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

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

如果想用連接符，需要在連接的項(xiàng)目之間拼接上連接符。

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

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

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

只有分隔符為null時(shí)才返回null，如果項(xiàng)目為null時(shí)，則不拼接

查詢語(yǔ)句：
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é)和字符獲取字符串的長(zhǎng)度

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

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

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

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

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

查詢語(yǔ)句：
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ù)來檢查摘錄列的長(zhǎng)度是否大于20，并使用CONCAT語(yǔ)句將excerpt列的值與省略號(hào)(...)連接起來，否則只是獲取全部摘錄(excerpt)內(nèi)容

3.left

返回具有指定長(zhǎng)度的字符串的左邊部分

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

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

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

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

如果length為0或?yàn)樨?fù)，則LEFT函數(shù)返回一個(gè)空字符串。如果length大于str字符串的長(zhǎng)度，則LEFT函數(shù)返回整個(gè)str字符串。

4.right

回具有指定長(zhǎng)度的字符串的右邊部分

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

5.replace

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

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

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

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

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

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

結(jié)果沒有發(fā)生變化，因?yàn)閰^(qū)分大小寫

6.substring

從具有特定長(zhǎng)度的位置開始提取一個(gè)子字符串。

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

有兩個(gè)參數(shù)：

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

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

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

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

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

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

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

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

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

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

查詢語(yǔ)句：
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的用法

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

查詢語(yǔ)句：
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選項(xiàng)明確指示TRIM()函數(shù)從字符串中刪除前導(dǎo)，尾隨或前導(dǎo)和尾隨的不必要的字符。

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

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

查詢語(yǔ)句：
//從字符串中除去前導(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

在逗號(hào)分隔的字符串列表中找到一個(gè)字符串

FIND_IN_SET(needle,haystack);

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

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

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

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

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

查詢語(yǔ)句：
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運(yùn)算符具有相同的功能

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

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

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

9.format

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

FOMRAT(N,D,locale);

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

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

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

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

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

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

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

總結(jié)

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

最新評(píng)論