SQL 中的高級字符處理函數
CHARINDEX
作用
會在第二個字符表達式中搜索一個字符表達式，這將返回第一個表達式（如果發現存在）的開始位置。
語法
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
注：這是一個常用的字符搜索函數，起始下標是1，不是0。
如果加了起始下標，會從忽略起始下標前面的字符，往后面搜索。
不加起始值示例
SELECT CHARINDEX('數據',
'SQL數據庫開發SQL數據庫開發')結果
圖片
添加起始值示例
SELECT CHARINDEX('數據',
'SQL數據庫開發SQL數據庫開發',6)結果
圖片
CONCAT_WS
作用
以端到端的方式返回從串聯或聯接的兩個或更多字符串值生成的字符串。它會用第一個函數參數中指定的分隔符分隔連接的字符串值。（CONCAT_WS 指示使用分隔符連接。）
語法
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
注：CONCAT_WS 會忽略列中的 NULL 值。用 ISNULL 函數包裝可以為 null 的列，并提供默認值。
示例
SELECT CONCAT_WS('-','SQL',
'數據庫',NULL,'開發')結果：
圖片
FORMAT
作用
返回使用指定格式和可選區域性格式化的值。使用 FORMAT 函數將日期/時間和數字值格式化為識別區域設置的字符串。對于一般的數據類型轉換，請使用 CAST 或 CONVERT。
語法
FORMAT ( value, format [, culture ] )
注意:
- FORMAT 將返回 NULL 錯誤，而不是非 valid 的 culture 。例如，如果 format 中指定的值無效，則返回 NULL 。
- FORMAT 函數具有不確定性。
- FORMAT 依賴于 .NET Framework 公共語言運行時 (CLR) 的存在。
- 此函數無法進行遠程處理，因為它依賴于 CLR 的存在。遠程處理需要 CLR 的函數可能導致在遠程服務器上出現錯誤。
日期格式示例
DECLARE @d DATETIME = '7/25/2024';
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 '中文格式';結果
圖片
圖片
自定義格式示例
SELECT FORMAT( GETDATE(),
'dd/MM/yyyy', 'zh-cn' ) AS '自定義日期'
,FORMAT(123456789,
'###-##-####') AS '自定義數字';結果
圖片
數值格式示例
SELECT FORMAT(1.127456,
'N','zh-cn') AS '四舍五入格式'
,FORMAT(1.123456,
'G', 'zh-cn') AS '一般格式'
,FORMAT(1.123456,
'C', 'zh-cn') AS '貨幣格式'結果
圖片
PATINDEX
作用
返回模式在指定表達式中第一次出現的起始位置；如果在所有有效的文本和字符數據類型中都找不到該模式，則返回零。該函數與CHARINDEX比較相似
語法
PATINDEX ( '%pattern%' , expression )
注意
- 如果 pattern 或 expression 為 NULL，則 PATINDEX 返回 NULL 。
- PATINDEX 的起始位置為 1。
- PATINDEX 基于輸入的排序規則執行比較。若要以指定排序規則進行比較，則可以使用 COLLATE 將顯式排序規則應用于輸入。
示例
SELECT PATINDEX('%數據庫%', 'SQL數據庫開發');結果
圖片
在 PATINDEX 中使用通配符示例
使用 % 和 _ 通配符查找模式 '數'（后跟任意一個字符和 '庫'）在指定字符串中的開始位置（索引從 1 開始）
SELECT PATINDEX('%數_庫%', 'SQL數據庫開發');結果
圖片
REPLICATE
作用
以指定的次數重復字符串值。
語法
REPLICATE ( string_expression ,integer_expression )
示例
SELECT '2'+REPLICATE ('3',5)結果
圖片
REVERSE
作用
返回字符串值的逆序。
語法
REVERSE ( string_expression )
示例
SELECT REVERSE('SQL數據庫開發')結果
圖片
STRING_AGG
作用
串聯字符串表達式的值，并在其間放置分隔符值。不能在字符串末尾添加分隔符。
語法
STRING_AGG ( expression, separator ) [ <order_clause> ]
注意
- STRING_AGG 是一個聚合函數，用于提取行中的所有表達式，并將這些表達式串聯成一個字符串。表達式值隱式轉換為字符串類型，然后串聯在一起。隱式轉換為字符串的過程遵循現有的數據類型轉換規則。
- 如果輸入表達式的類型為 VARCHAR，則分隔符的類型不能是 NVARCHAR。
- null 值會被忽略，且不會添加相應的分隔符。若要為 null 值返回占位符，請使用 ISNULL 函數，如示例 B 中所示。
- STRING_AGG 適用于任何兼容級別。
示例
WITH t AS (
SELECT '張三' Name,'語文' Course,89 Score
UNION ALL
SELECT '張三' ,'數學' ,91
UNION ALL
SELECT '李四' ,'語文' ,78
UNION ALL
SELECT '李四' ,'數學' ,96
)
SELECT Name,
STRING_AGG(Course,',') Course ,
STRING_AGG(Score,',') Score
FROM t
GROUP BY Name結果
圖片
STRING_SPLIT
作用
一個表值函數，它根據指定的分隔符將字符串拆分為子字符串行。
語法
STRING_SPLIT ( string , separator )
注意
- STRING_SPLIT 輸入包含分隔子字符串的字符串，并輸入一個字符用作分隔符。STRING_SPLIT 輸出其行包含子字符串的單列表。輸出列的名稱為“value”。
- 輸出行可以按任意順序排列。順序不保證與輸入字符串中的子字符串順序匹配。可以通過在 SELECT 語句中使用 ORDER BY 子句覆蓋最終排序順序 (ORDER BY value)。
- 當輸入字符串包含兩個或多個連續出現的分隔符字符時，將出現長度為零的空子字符串。空子字符串的處理方式與普通子字符串相同。可以通過使用 WHERE 子句篩選出包含空的子字符串的任何行 (WHERE value <> '')。如果輸入字符串為 NULL，則 STRING_SPLIT 表值函數返回一個空表。
示例一
SELECT Value FROM
STRING_SPLIT('SQL-數據庫-開發', '-');結果
圖片
示例二
WITH t AS (
SELECT 1 ID,'張三' Name,
'足球,籃球,羽毛球' Hobby
UNION ALL
SELECT 2 ,'李四','足球,游泳,爬山'
)
SELECT ID, Name, Value
FROM t
CROSS APPLY STRING_SPLIT(Hobby, ',');結果
圖片
注意：返回的列不再是Hobby，而是Value，必須寫成Value，否則得不到想要的結果。這個與STRING_AGG()函數的功能相反。
STUFF
作用
STUFF 函數將字符串插入到另一個字符串中。它從第一個字符串的開始位置刪除指定長度的字符；然后將第二個字符串插入到第一個字符串的開始位置。
語法
STUFF ( character_expression , start , length , replaceWith_expression )
注意
- 如果開始位置或長度值是負數，或者開始位置大于第一個字符串的長度，則返回 Null 字符串。如果開始位置為 0，則返回 Null 值。如果要刪除的長度大于第一個字符串的長度，則刪除到第一個字符串中的第一個字符。
- 如果結果值大于返回類型支持的最大值，則會引發錯誤。
示例
SELECT STUFF('abcdef', 2, 3, 'ijklmn');結果
圖片
