常用SQL函數(shù)太簡(jiǎn)單？那是你沒(méi)用過(guò)這些高級(jí)函數(shù)
陸續(xù)分享了幾套數(shù)據(jù)庫(kù)平臺(tái)的常用函數(shù)，就有讀者反映：總是這些簡(jiǎn)單的知識(shí)點(diǎn)搗騰來(lái)?yè)v騰去，有意思嗎？敢不敢來(lái)點(diǎn)高級(jí)的？
讀者有需要，怎么可能不滿足呢？但是又要講的大家都明白，所以花了不少時(shí)間做示例演示，今天就分享給大家。
測(cè)試環(huán)境
SQL Server 2017
CHARINDEX
作用
會(huì)在第二個(gè)字符表達(dá)式中搜索一個(gè)字符表達(dá)式，這將返回第一個(gè)表達(dá)式（如果發(fā)現(xiàn)存在）的開始位置。
語(yǔ)法
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
注：這是一個(gè)常用的字符搜索函數(shù)，起始下標(biāo)是1，不是0。
如果加了起始下標(biāo)，會(huì)從忽略起始下標(biāo)前面的字符，往后面搜索。
不加起始值示例
- SELECT CHARINDEX('數(shù)據(jù)','SQL數(shù)據(jù)庫(kù)開發(fā)SQL數(shù)據(jù)庫(kù)開發(fā)')
結(jié)果
添加起始值示例
- SELECT CHARINDEX('數(shù)據(jù)','SQL數(shù)據(jù)庫(kù)開發(fā)SQL數(shù)據(jù)庫(kù)開發(fā)',6)
結(jié)果
CONCAT_WS
作用
以端到端的方式返回從串聯(lián)或聯(lián)接的兩個(gè)或更多字符串值生成的字符串。 它會(huì)用第一個(gè)函數(shù)參數(shù)中指定的分隔符分隔連接的字符串值。 （CONCAT_WS 指示使用分隔符連接。）
語(yǔ)法
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
注：CONCAT_WS 會(huì)忽略列中的 NULL 值。 用 ISNULL 函數(shù)包裝可以為 null 的列，并提供默認(rèn)值。
示例
- SELECT CONCAT_WS('-','SQL','數(shù)據(jù)庫(kù)',NULL,'開發(fā)')
結(jié)果：
SOUNDEX
作用
返回一個(gè)由四個(gè)字符組成的代碼 (SOUNDEX)，用于評(píng)估兩個(gè)字符串的相似性。
語(yǔ)法
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é)果：
說(shuō)明兩個(gè)的發(fā)音非常接近
DIFFERENCE
作用
返回一個(gè)整數(shù)值，用于度量?jī)蓚€(gè)不同字符表達(dá)式的 SOUNDEX() 值之間的差異
語(yǔ)法
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ù)字值格式化為識(shí)別區(qū)域設(shè)置的字符串。 對(duì)于一般的數(shù)據(jù)類型轉(zhuǎn)換，請(qǐng)使用 CAST 或 CONVERT。
語(yǔ)法
FORMAT ( value, format [, culture ] )
注意
- FORMAT 將返回 NULL 錯(cuò)誤，而不是非 valid 的 culture 。 例如，如果 format 中指定的值無(wú)效，則返回 NULL 。
- FORMAT 函數(shù)具有不確定性。
- FORMAT 依賴于 .NET Framework 公共語(yǔ)言運(yùn)行時(shí) (CLR) 的存在。
- 此函數(shù)無(wú)法進(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 '美國(guó)格式'
- ,FORMAT ( @d, 'd', 'en-gb' ) AS '英國(guó)格式'
- ,FORMAT ( @d, 'd', 'de-de' ) AS '德國(guó)格式'
- ,FORMAT ( @d, 'd', 'zh-cn' ) AS '中文格式';
- SELECT FORMAT ( @d, 'D', 'en-US' ) AS '美國(guó)格式'
- ,FORMAT ( @d, 'D', 'en-gb' ) AS '英國(guó)格式'
- ,FORMAT ( @d, 'D', 'de-de' ) AS '德國(guó)格式'
- ,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比較相似
語(yǔ)法
PATINDEX ( '%pattern%' , expression )
注：
- 如果 pattern 或 expression 為 NULL，則 PATINDEX 返回 NULL 。
- PATINDEX 的起始位置為 1。
- PATINDEX 基于輸入的排序規(guī)則執(zhí)行比較。 若要以指定排序規(guī)則進(jìn)行比較，則可以使用 COLLATE 將顯式排序規(guī)則應(yīng)用于輸入。
示例
- SELECT PATINDEX('%數(shù)據(jù)庫(kù)%', 'SQL數(shù)據(jù)庫(kù)開發(fā)');
結(jié)果
在 PATINDEX 中使用通配符示例
使用 % 和 _ 通配符查找模式 '數(shù)'（后跟任意一個(gè)字符和 '庫(kù)'）在指定字符串中的開始位置（索引從 1 開始）
- SELECT PATINDEX('%數(shù)_庫(kù)%', 'SQL數(shù)據(jù)庫(kù)開發(fā)');
結(jié)果
QUOTENAME
作用
返回帶有分隔符的 Unicode 字符串，分隔符的加入可使輸入的字符串成為有效的 SQL Server 分隔標(biāo)識(shí)符。
語(yǔ)法
QUOTENAME ( 'character_string' [ , 'quote_character' ] )
注
'character_string'
Unicode 字符數(shù)據(jù)構(gòu)成的字符串。 character_string 是 sysname，且最多具有 128 個(gè)字符 。 超過(guò) 128 個(gè)字符的輸入將返回 NULL。
'quote_character'
用作分隔符的單字符字符串。 可以是單引號(hào) (')、左方括號(hào)或右方括號(hào) ([])、雙引號(hào) (")、左圓括號(hào)或右圓括號(hào) (())、大于或小于符號(hào) (><)、左大括號(hào)或右大括號(hào) ({}) 或反引號(hào) (`) 。 如果提供了不可接受的字符，則返回 NULL。 如果未指定 quote_character，則使用方括號(hào) 。
示例
- SELECT QUOTENAME('abc[]def');
結(jié)果
REPLICATE
作用
以指定的次數(shù)重復(fù)字符串值。
語(yǔ)法
( string_expression ,integer_expression )
示例
- SELECT '2'+REPLICATE ('3',5)
結(jié)果
REVERSE
作用
返回字符串值的逆序。
語(yǔ)法
REVERSE ( string_expression )
示例
- SELECT REVERSE('SQL數(shù)據(jù)庫(kù)開發(fā)')
結(jié)果
STRING_AGG
作用
串聯(lián)字符串表達(dá)式的值，并在其間放置分隔符值。 不能在字符串末尾添加分隔符。
語(yǔ)法
STRING_AGG ( expression, separator ) [ <order_clause> ]
注意：
- STRING_AGG 是一個(gè)聚合函數(shù)，用于提取行中的所有表達(dá)式，并將這些表達(dá)式串聯(lián)成一個(gè)字符串。 表達(dá)式值隱式轉(zhuǎn)換為字符串類型，然后串聯(lián)在一起。 隱式轉(zhuǎn)換為字符串的過(guò)程遵循現(xiàn)有的數(shù)據(jù)類型轉(zhuǎn)換規(guī)則。
- 如果輸入表達(dá)式的類型為 VARCHAR，則分隔符的類型不能是 NVARCHAR。
- null 值會(huì)被忽略，且不會(huì)添加相應(yīng)的分隔符。 若要為 null 值返回占位符，請(qǐng)使用 ISNULL 函數(shù)，如示例 B 中所示。
- STRING_AGG 適用于任何兼容級(jí)別。
示例
- WITH t AS (
- SELECT '張三' Name,'語(yǔ)文' Course,89 Score
- UNION ALL
- SELECT '張三' ,'數(shù)學(xué)' ,91
- UNION ALL
- SELECT '李四' ,'語(yǔ)文' ,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ù)指定的分隔符將字符串拆分為子字符串行。
語(yǔ)法
STRING_SPLIT ( string , separator )
注意
STRING_SPLIT 輸入包含分隔子字符串的字符串，并輸入一個(gè)字符用作分隔符。 STRING_SPLIT 輸出其行包含子字符串的單列表。 輸出列的名稱為“value”。
輸出行可以按任意順序排列。 順序不保證與輸入字符串中的子字符串順序匹配。 可以通過(guò)在 SELECT 語(yǔ)句中使用 ORDER BY 子句覆蓋最終排序順序 (ORDER BY value)。
當(dāng)輸入字符串包含兩個(gè)或多個(gè)連續(xù)出現(xiàn)的分隔符字符時(shí)，將出現(xiàn)長(zhǎng)度為零的空子字符串。 空子字符串的處理方式與普通子字符串相同。 可以通過(guò)使用 WHERE 子句篩選出包含空的子字符串的任何行 (WHERE value <> '')。 如果輸入字符串為 NULL，則 STRING_SPLIT 表值函數(shù)返回一個(gè)空表。
示例一
- SELECT Value FROM STRING_SPLIT('SQL-數(shù)據(jù)庫(kù)-開發(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è)字符串的開始位置刪除指定長(zhǎng)度的字符；然后將第二個(gè)字符串插入到第一個(gè)字符串的開始位置。
語(yǔ)法
STUFF ( character_expression , start , length , replaceWith_expression )
注意：
- 如果開始位置或長(zhǎng)度值是負(fù)數(shù)，或者開始位置大于第一個(gè)字符串的長(zhǎng)度，則返回 Null 字符串。 如果開始位置為 0，則返回 Null 值。如果要?jiǎng)h除的長(zhǎng)度大于第一個(gè)字符串的長(zhǎng)度，則刪除到第一個(gè)字符串中的第一個(gè)字符。
- 如果結(jié)果值大于返回類型支持的最大值，則會(huì)引發(fā)錯(cuò)誤。
示例
- SELECT STUFF('abcdef', 2, 3, 'ijklmn');
結(jié)果
以上就是今天要給大家介紹的高級(jí)字符函數(shù)，下次我們?cè)俳o大家介紹高級(jí)日期函數(shù)的相關(guān)用法。
