點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫開發(fā)”，

設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨

幾個(gè)月前整理一套SQL Server的常用函數(shù)，還沒看或想收藏的戳這里《精心整理了一套SQL Server常用函數(shù)，速速收藏！》。

后來又陸續(xù)分享了幾套其他數(shù)據(jù)庫平臺(tái)的常用函數(shù)，就有讀者反映：總是這些簡單的知識點(diǎn)搗騰來搗騰去，有意思嗎？敢不敢來點(diǎn)高級的？

小伙伴有需要，怎么可能不滿足呢？但是又要講的大家都明白，所以花了不少時(shí)間做示例演示，今天就分享給大家。

測試環(huán)境

SQL Server 2017

CHARINDEX

作用

會(huì)在第二個(gè)字符表達(dá)式中搜索一個(gè)字符表達(dá)式，這將返回第一個(gè)表達(dá)式（如果發(fā)現(xiàn)存在）的開始位置。

語法

CHARINDEX?(?expressionToFind?,?expressionToSearch?[?,?start_location?]?)

注：這是一個(gè)常用的字符搜索函數(shù)，起始下標(biāo)是1，不是0。

如果加了起始下標(biāo)，會(huì)從忽略起始下標(biāo)前面的字符，往后面搜索。

不加起始值示例

SELECT?CHARINDEX('數(shù)據(jù)','SQL數(shù)據(jù)庫開發(fā)SQL數(shù)據(jù)庫開發(fā)')

結(jié)果

添加起始值示例

SELECT?CHARINDEX('數(shù)據(jù)','SQL數(shù)據(jù)庫開發(fā)SQL數(shù)據(jù)庫開發(fā)',6)

結(jié)果

CONCAT_WS

作用

以端到端的方式返回從串聯(lián)或聯(lián)接的兩個(gè)或更多字符串值生成的字符串。?它會(huì)用第一個(gè)函數(shù)參數(shù)中指定的分隔符分隔連接的字符串值。?（CONCAT_WS?指示使用分隔符連接。）

語法

CONCAT_WS?(?separator,?argument1,?argument2?[,?argumentN]...?)

注：CONCAT_WS 會(huì)忽略列中的 NULL 值。?用?ISNULL?函數(shù)包裝可以為 null 的列，并提供默認(rèn)值。

示例

SELECT?CONCAT_WS('-','SQL','數(shù)據(jù)庫',NULL,'開發(fā)')

結(jié)果：

SOUNDEX

作用

返回一個(gè)由四個(gè)字符組成的代碼 (SOUNDEX)，用于評估兩個(gè)字符串的相似性。

語法

SOUNDEX?(?character_expression?)

注：SOUNDEX 會(huì)根據(jù)字符串的發(fā)音，將字母數(shù)字字符串轉(zhuǎn)換成一個(gè)由四個(gè)字符組成的代碼。?該代碼的第一個(gè)字符是 character_expression 的第一個(gè)字符，已轉(zhuǎn)換為大寫。?代碼的第二個(gè)字符到第四個(gè)字符是表示表達(dá)式中的字母的數(shù)字。?除非字母 A、E、I、O、U、H、W 和 Y 是字符串的首字母，否則將忽略這些字母。?如果需要生成一個(gè)四字符代碼，將在末尾添加零

示例

SELECT?SOUNDEX?('liyue'), SOUNDEX?('liyuee')

結(jié)果：

說明兩個(gè)的發(fā)音非常接近

DIFFERENCE

作用

返回一個(gè)整數(shù)值，用于度量兩個(gè)不同字符表達(dá)式的?SOUNDEX()?值之間的差異

語法

DIFFERENCE?(?character_expression?,?character_expression?)

注：DIFFERENCE?比較兩個(gè)不同的?SOUNDEX?值，并返回一個(gè)整數(shù)值。?該值用于度量?SOUNDEX?值匹配的程度，范圍為 0 到 4。?值為 0 表示 SOUNDEX 值之間的相似性較弱或不相似；4 表示與 SOUNDEX 值非常相似，甚至完全相同。

DIFFERENCE?和?SOUNDEX?具有排序規(guī)則敏感度。

示例

SELECT?
SOUNDEX?('liyue'), SOUNDEX?('liyuee') ,
DIFFERENCE('liyue', 'liyuee');

結(jié)果：

返回的結(jié)果為4，進(jìn)一步驗(yàn)證了我們上一個(gè)函數(shù)。

FORMAT

作用

返回使用指定格式和可選區(qū)域性格式化的值。?使用 FORMAT 函數(shù)將日期/時(shí)間和數(shù)字值格式化為識別區(qū)域設(shè)置的字符串。?對于一般的數(shù)據(jù)類型轉(zhuǎn)換，請使用 CAST 或 CONVERT。

語法

FORMAT?(?value,?format?[,?culture?]?)

注意

FORMAT 將返回 NULL 錯(cuò)誤，而不是非 valid 的 culture 。?例如，如果 format 中指定的值無效，則返回 NULL 。
FORMAT 函數(shù)具有不確定性。
FORMAT 依賴于 .NET Framework 公共語言運(yùn)行時(shí) (CLR) 的存在。
此函數(shù)無法進(jìn)行遠(yuǎn)程處理，因?yàn)樗蕾囉?CLR 的存在。?遠(yuǎn)程處理需要 CLR 的函數(shù)可能導(dǎo)致在遠(yuǎn)程服務(wù)器上出現(xiàn)錯(cuò)誤。

日期格式示例

DECLARE?@d DATETIME = '12/18/2019';
SELECT?FORMAT?( @d, 'd', 'en-US'?) AS?'美國格式'??
??????,FORMAT?( @d, 'd', 'en-gb'?) AS?'英國格式'??
??????,FORMAT?( @d, 'd', 'de-de'?) AS?'德國格式'??
??????,FORMAT?( @d, 'd', 'zh-cn'?) AS?'中文格式';
??
SELECT?FORMAT?( @d, 'D', 'en-US'?) AS?'美國格式'??
??????,FORMAT?( @d, 'D', 'en-gb'?) AS?'英國格式'??
??????,FORMAT?( @d, 'D', 'de-de'?) AS?'德國格式'??
??????,FORMAT?( @d, 'D', 'zh-cn'?) AS?'中文格式';

結(jié)果

自定義格式示例

SELECT?FORMAT( GETDATE(), 'dd/MM/yyyy', 'zh-cn'?) AS?'自定義日期'??
???????,FORMAT(123456789,'###-##-####') AS?'自定義數(shù)字';

結(jié)果

數(shù)值格式示例

SELECT?FORMAT(1.127456, 'N', 'zh-cn') AS?'四舍五入格式'??
???????,FORMAT(1.123456, 'G', 'zh-cn') AS?'一般格式'??
???????,FORMAT(1.123456, 'C', 'zh-cn') AS?'貨幣格式'

結(jié)果

PATINDEX

作用

返回模式在指定表達(dá)式中第一次出現(xiàn)的起始位置；如果在所有有效的文本和字符數(shù)據(jù)類型中都找不到該模式，則返回零。該函數(shù)與CHARINDEX比較相似

語法

PATINDEX?(?'%pattern%'?,?expression?)

注：

如果 pattern 或 expression 為 NULL，則 PATINDEX 返回 NULL 。
PATINDEX 的起始位置為 1。
PATINDEX 基于輸入的排序規(guī)則執(zhí)行比較。?若要以指定排序規(guī)則進(jìn)行比較，則可以使用 COLLATE 將顯式排序規(guī)則應(yīng)用于輸入。

示例

SELECT?PATINDEX('%數(shù)據(jù)庫%', 'SQL數(shù)據(jù)庫開發(fā)');

結(jié)果

在 PATINDEX 中使用通配符示例

使用 % 和 _ 通配符查找模式?'數(shù)'（后跟任意一個(gè)字符和?'庫'）在指定字符串中的開始位置（索引從 1 開始）

SELECT?PATINDEX('%數(shù)_庫%', 'SQL數(shù)據(jù)庫開發(fā)');

結(jié)果

QUOTENAME

作用

返回帶有分隔符的 Unicode 字符串，分隔符的加入可使輸入的字符串成為有效的?SQL Server?分隔標(biāo)識符。

語法

QUOTENAME?(?'character_string'?[?,?'quote_character'?]?)

注

'character_string'?
Unicode 字符數(shù)據(jù)構(gòu)成的字符串。?character_string 是 sysname，且最多具有 128 個(gè)字符。?超過 128 個(gè)字符的輸入將返回 NULL。

'quote_character'?
用作分隔符的單字符字符串。?可以是單引號 (')、左方括號或右方括號 ([])、雙引號 (")、左圓括號或右圓括號 (())、大于或小于符號 (><)、左大括號或右大括號 ({}) 或反引號 (`) 。?如果提供了不可接受的字符，則返回 NULL。?如果未指定 quote_character，則使用方括號。

示例

SELECT?QUOTENAME('abc[]def');

結(jié)果

REPLICATE

作用

以指定的次數(shù)重復(fù)字符串值。

語法

(?string_expression?,integer_expression?)

示例

SELECT?'2'+REPLICATE?('3',5)

結(jié)果

REVERSE

作用

返回字符串值的逆序。

語法

REVERSE?(?string_expression?)

示例

SELECT?REVERSE('SQL數(shù)據(jù)庫開發(fā)')

結(jié)果

STRING_AGG

作用

串聯(lián)字符串表達(dá)式的值，并在其間放置分隔符值。?不能在字符串末尾添加分隔符。

語法

STRING_AGG?(?expression,?separator?)?[??]

注意：

STRING_AGG?是一個(gè)聚合函數(shù)，用于提取行中的所有表達(dá)式，并將這些表達(dá)式串聯(lián)成一個(gè)字符串。?表達(dá)式值隱式轉(zhuǎn)換為字符串類型，然后串聯(lián)在一起。?隱式轉(zhuǎn)換為字符串的過程遵循現(xiàn)有的數(shù)據(jù)類型轉(zhuǎn)換規(guī)則。?
如果輸入表達(dá)式的類型為?VARCHAR，則分隔符的類型不能是?NVARCHAR。
null 值會(huì)被忽略，且不會(huì)添加相應(yīng)的分隔符。?若要為 null 值返回占位符，請使用?ISNULL?函數(shù)，如示例 B 中所示。
STRING_AGG?適用于任何兼容級別。

示例

WITH t AS (
?SELECT?'張三'?Name,'語文'?Course,89?Score
?UNION?ALL
?SELECT?'張三'?,'數(shù)學(xué)'?,91?
??UNION?ALL
?SELECT?'李四'?,'語文'?,78?
??UNION?ALL
?SELECT?'李四'?,'數(shù)學(xué)'?,96?
?)

?SELECT?Name,
?STRING_AGG(Course,',') Course ,
?STRING_AGG(Score,',') Score
?FROM?t
?GROUP??BY?Name

結(jié)果

STRING_SPLIT

作用

一個(gè)表值函數(shù)，它根據(jù)指定的分隔符將字符串拆分為子字符串行。

語法

STRING_SPLIT?(?string?,?separator?)

注意

STRING_SPLIT 輸入包含分隔子字符串的字符串，并輸入一個(gè)字符用作分隔符。?STRING_SPLIT 輸出其行包含子字符串的單列表。?輸出列的名稱為“value”。

輸出行可以按任意順序排列。?順序不保證與輸入字符串中的子字符串順序匹配。?可以通過在 SELECT 語句中使用 ORDER BY 子句覆蓋最終排序順序 (ORDER BY value)。

當(dāng)輸入字符串包含兩個(gè)或多個(gè)連續(xù)出現(xiàn)的分隔符字符時(shí)，將出現(xiàn)長度為零的空子字符串。?空子字符串的處理方式與普通子字符串相同。?可以通過使用 WHERE 子句篩選出包含空的子字符串的任何行 (WHERE value <> '')。?如果輸入字符串為 NULL，則 STRING_SPLIT 表值函數(shù)返回一個(gè)空表。

示例一

SELECT?Value?FROM?STRING_SPLIT('SQL-數(shù)據(jù)庫-開發(fā)', '-');

結(jié)果

示例二

WITH t AS (
?SELECT?1?ID,'張三'?Name,'足球,籃球,羽毛球'?Hobby
?UNION?ALL
?SELECT?2?,'李四','足球,游泳,爬山'
?)


SELECT?ID, Name, Value
FROM?t
CROSS?APPLY?STRING_SPLIT(Hobby, ',');

結(jié)果

注意：返回的列不再是Hobby，而是Value，必須寫成Value，否則得不到想要的結(jié)果。這個(gè)與STRING_AGG()函數(shù)的功能相反。

STUFF

作用

STUFF 函數(shù)將字符串插入到另一個(gè)字符串中。?它從第一個(gè)字符串的開始位置刪除指定長度的字符；然后將第二個(gè)字符串插入到第一個(gè)字符串的開始位置。

語法

STUFF?(?character_expression?,?start?,?length?,?replaceWith_expression?)

注意：

如果開始位置或長度值是負(fù)數(shù)，或者開始位置大于第一個(gè)字符串的長度，則返回 Null 字符串。?如果開始位置為 0，則返回 Null 值。如果要?jiǎng)h除的長度大于第一個(gè)字符串的長度，則刪除到第一個(gè)字符串中的第一個(gè)字符。
如果結(jié)果值大于返回類型支持的最大值，則會(huì)引發(fā)錯(cuò)誤。

示例

SELECT?STUFF('abcdef', 2, 3, 'ijklmn');

結(jié)果

以上就是今天要給大家介紹的高級字符函數(shù)，下次我們再給大家介紹高級日期函數(shù)的相關(guān)用法。

碼字不易，覺得不錯(cuò)，記得轉(zhuǎn)發(fā)分享給更多人，謝謝啦~

——End——
后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。
推薦閱讀
從今天起，職場再無帶薪拉屎！
一款SQL自動(dòng)檢查神器，再也不用擔(dān)心SQL出錯(cuò)了！
SQL 語句中 where 條件后 寫上1=1 是什么意思
國產(chǎn)數(shù)據(jù)庫建模工具，看到界面第一眼，良心了！
快手公司廁所裝坑位計(jì)時(shí)器，網(wǎng)友：再也不能帶薪拉屎了！

這是一個(gè)能學(xué)到技術(shù)的公眾號，歡迎關(guān)注
點(diǎn)擊「閱讀原文」了解SQL訓(xùn)練營

精心整理了一套SQL高級函數(shù)，建議收藏

CONCAT_WS

DIFFERENCE

PATINDEX

在 PATINDEX 中使用通配符示例

REVERSE