不少人一開始接觸 SQL Server 里的字符串函數(shù)都是從LEN、SUBSTRING這類單個函數(shù)用起等真正碰到復(fù)雜一點(diǎn)的報表、ETL、接口數(shù)據(jù)清洗時才發(fā)現(xiàn)這些零散函數(shù)怎么拼都不順手。第 41 章筆記我就打算換個思路不按函數(shù)名羅列一遍而是把字符串函數(shù)當(dāng)成一組可以組合使用的工具箱按實際需求來講它們各自扮演什么角色、有哪些坑、怎樣搭配才能組合出自己想要的效果。這篇內(nèi)容適合剛把 T-SQL 基礎(chǔ)語法過完、準(zhǔn)備上手寫真實查詢的人也適合寫了幾年 SQL 但遇到字符串處理還是習(xí)慣去網(wǎng)上臨時搜函數(shù)的開發(fā)者把常用函數(shù)的行為邊界和內(nèi)部邏輯摸清楚了很多代碼其實不用反復(fù)試錯。1. 字符串函數(shù)全景與使用場景1.1 先理清 SQL Server 里的字符串處理生態(tài)SQL Server 中的字符串函數(shù)分布在幾個不同的類別里很多人容易混淆。一類是純標(biāo)量函數(shù)也就是輸入一個或幾個字符串輸出一個新字符串或一個數(shù)值比如LEN、UPPER、SUBSTRING這類。另一類是較新的表值函數(shù)最典型的就是STRING_SPLIT它把一串文本拆成一張表可以和JOIN、IN配合使用。還有一類屬于聚合函數(shù)典型代表是STRING_AGG它把多行的值合并成單個字符串。這幾類的使用場景完全不同把它們混在一起記遇到實際問題時就會容易犯方向性錯誤。舉個例子你要把某個訂單表里的多個商品名稱拼到一個單元格里如果只知道CONCAT就麻煩了。CONCAT是行內(nèi)的拼接處理不了多行合并STRING_AGG才是做跨行拼接的。反過來你想判斷某個字符串里是否包含某個子串SUBSTRING是做截取CHARINDEX才是查位置。搞清楚“這個函數(shù)解決的是哪個維度的問題”比死記函數(shù)簽名更重要。1.2 從業(yè)務(wù)視角看字符串函數(shù)的價值字符串處理在業(yè)務(wù)里并不是花哨炫技而是每天都在用的基礎(chǔ)能力。比如客戶姓名清洗用戶錄入時可能帶了前后空格、中間夾雜全角空格或者大小寫不統(tǒng)一再比如接口對接時拿到的原始報文里日期格式是2024-01-05T10:30:00Z要在 SQL 里轉(zhuǎn)成標(biāo)準(zhǔn)日期就必須先用字符串函數(shù)把T和Z處理掉還有報表開發(fā)時要把幾個維度的文本拼接成展示列或者把明細(xì)數(shù)據(jù)按某個分組拼成一行。這類工作如果全靠應(yīng)用程序代碼做一是浪費(fèi)網(wǎng)絡(luò)帶寬二是處理邏輯分散在業(yè)務(wù)系統(tǒng)里排查問題困難。在 SQL 層直接用字符串函數(shù)處理速度快、邏輯集中、可維護(hù)性也更高。從這個角度看字符串函數(shù)不是“會不會用”的問題而是“能不能用得好”的問題。很多性能問題之所以出現(xiàn)往往不是某個函數(shù)本身慢而是因為沒有理解函數(shù)的內(nèi)部運(yùn)作方式導(dǎo)致代碼寫得繞、掃描次數(shù)多、隱式轉(zhuǎn)換頻繁。2. 核心函數(shù)拆解從返回值到邊界行為2.1 測量類函數(shù)LEN 與 DATALENGTH 的區(qū)別先說最容易混淆的一對LEN和DATALENGTH。LEN返回字符串的字符數(shù)而且它會自動去掉結(jié)尾的空格DATALENGTH返回字節(jié)數(shù)它不會去空格。這兩個函數(shù)的結(jié)果只在一種情況下完全一致就是字符串里不包含中文字符并且結(jié)尾沒有空格的時候。實際工作中最常踩的坑是下面這種表中某個字段是NVARCHAR(50)你查詢WHERE LEN(column) 6以為過濾的是“6個字符”的數(shù)據(jù)但如果字段里存了類似“測試 “這樣帶尾隨空格的值實際字符數(shù)只有 2。由于LEN忽略尾部空格你可能把本應(yīng)匹配的值漏掉了。反過來如果用了DATALENGTH(column) 12因為中文在NCHAR/NVARCHAR里占 2 字節(jié)一個“數(shù)據(jù)庫”三個字是 6 字節(jié)而非 3 字節(jié)所以數(shù)值判斷會完全不同。我自己的習(xí)慣是需要判斷“是否為空串”或者“是否超過某長度”時優(yōu)先用DATALENGTH配合去除空格后的結(jié)果而不是單用LEN因為默認(rèn)的LEN行為在數(shù)據(jù)質(zhì)量參差不齊的表中會產(chǎn)生誤導(dǎo)。不過要提醒一點(diǎn)DATALENGTH對NULL也返回NULL所以使用時通常先用ISNULL或COALESCE做保護(hù)。2.2 查找類函數(shù)CHARINDEX、PATINDEX 與字符串定位CHARINDEX是查找子串位置的函數(shù)它返回的是子串在字符串中第一次出現(xiàn)的起始位置如果找不到就返回 0。比如CHARINDEX(, testexample.com)返回 5。第三個參數(shù)可以指定從第幾位開始查找這在處理一個字符串里出現(xiàn)多次相同分隔符時非常有用。PATINDEX跟CHARINDEX的寫法不同它支持模式匹配類似于LIKE中的通配符。比如你在一堆地址里找“數(shù)字路”這種模式可以寫成PATINDEX(%[0-9]路%, address)。它返回的是符合模式的起始位置不是布爾值。這在解析不規(guī)則文本時非常有用但也容易犯一個錯誤寫PATINDEX時忘了模式前后的%。沒加%意味著必須從字符串開頭精確匹配到結(jié)尾絕大多數(shù)情況下你會得到 0然后懷疑數(shù)據(jù)有問題。另外一個常見誤區(qū)是把CHARINDEX的位置和字符串下標(biāo)搞混。SQL Server 的字符串位置是從 1 開始的不是從 0 開始這和 C#、Java 的substring行為完全不同。所以當(dāng)你在應(yīng)用層已經(jīng)習(xí)慣了索引從 0 開始時到了 SQL 里很容易多減一次 1導(dǎo)致取出來的字符差一位。我之前排查過一個提取訂單號后四位的問題最終定位到就是位置參數(shù)差了 1結(jié)果整批數(shù)據(jù)都錯位了。2.3 提取類函數(shù)SUBSTRING、LEFT、RIGHT 的正確姿勢SUBSTRING是最常用的提取函數(shù)三個參數(shù)分別是原始字符串、起始位置、提取長度。它的行為有幾個特點(diǎn)需要注意。第一如果起始位置超過了字符串長度返回結(jié)果是空字符串而不是報錯。第二如果起始位置加上提取長度超過了字符串總長度它只返回到末尾的字符不報錯。這個特性在數(shù)據(jù)長度不夠時非常實用但也容易掩蓋問題讓你誤以為數(shù)據(jù)沒問題。LEFT和RIGHT更像是SUBSTRING的語法糖LEFT(abc, 2)等價于SUBSTRING(abc, 1, 2)RIGHT(abc, 2)等價于SUBSTRING(abc, LEN(abc) - 1, 2)。但要注意LEFT和RIGHT在處理中文時和SUBSTRING一樣都是按字符而不是字節(jié)來截取的。有人會誤以為它們按字節(jié)截取于是拿它處理二進(jìn)制或混有全角符號的字符串結(jié)果得到亂碼。如果你處理的字段里有表情符號或特殊 Unicode 字符情況會更復(fù)雜因為 SQL Server 的默認(rèn)排序規(guī)則可能把某些字符看作多個字節(jié)單純用LEFT有可能把代理對拆開。提取類函數(shù)在數(shù)據(jù)清洗中的經(jīng)典用法包括從身份證號里提取出生日期、從訂單號里提取地區(qū)碼、從郵箱地址里提取用戶名或域名。這些場景的關(guān)鍵點(diǎn)在于先用CHARINDEX定位分隔符再按位置截取而不是硬編碼固定長度。因為硬編碼長度在數(shù)據(jù)格式變化時很容易失控。2.4 改寫類函數(shù)REPLACE、STUFF 與正則的邊界REPLACE很好理解把字符串中的某段內(nèi)容替換成新內(nèi)容。但有一點(diǎn)需要注意REPLACE不是只替換第一次出現(xiàn)的匹配也不是用正則表達(dá)式而是把整個字符串中所有匹配的子串全部替換。想要只替換第一個出現(xiàn)的匹配SQL Server 沒有內(nèi)置的直接函數(shù)常規(guī)做法是用STUFF配合CHARINDEX自己實現(xiàn)。STUFF是很多開發(fā)者不太熟悉的函數(shù)它做的事情是從指定位置刪除指定長度的字符然后插入一個新的字符串。比如STUFF(abcdef, 2, 2, XY)的結(jié)果是aXYdef。這個函數(shù)在拼接動態(tài) SQL、修正固定格式文本時非常有用。舉個例子你要把訂單號中間幾位替換成星號用STUFF就很干凈STUFF(order_no, 4, 4, ****)。用REPLACE反而做不到這種按位置替換的效果。有些人會問REPLACE能不能實現(xiàn)正則替換答案是不能。SQL Server 原生函數(shù)里沒有正則替換。如果你真的需要按正則匹配替換內(nèi)容一般的選擇是使用CLR聚合函數(shù)或者調(diào)用OLE Automation存儲過程但這兩者都不是默認(rèn)推薦方案尤其在生產(chǎn)環(huán)境里限制多、風(fēng)險大。更常見、更穩(wěn)妥的做法是用PATINDEX定位模式再用STUFF多次替換。雖然代碼會長一些但至少純 T-SQL 就能完成不需要額外部署組件。2.5 轉(zhuǎn)換與規(guī)范化UPPER、LOWER、LTRIM、RTRIM 的隱藏作用大小寫轉(zhuǎn)換和去空格函數(shù)看起來簡單但在實際數(shù)據(jù)清洗中作用很大。UPPER和LOWER用于把數(shù)據(jù)轉(zhuǎn)換成統(tǒng)一大小寫常見于用戶名或郵箱匹配場景。注意一點(diǎn)SQL Server 的排序規(guī)則決定了大寫字母和對應(yīng)小寫字母的默認(rèn)比較行為。如果數(shù)據(jù)庫排序規(guī)則是Chinese_PRC_CI_AS默認(rèn)是大小寫不敏感_CI表示 case-insensitive所以WHERE name ABC和WHERE name abc在沒有強(qiáng)制排序規(guī)則時的結(jié)果一樣。此時你再費(fèi)勁去UPPER列值可能白白增加了計算開銷卻沒有實際意義。只有在排序規(guī)則區(qū)分大小寫如_CS_AS時大小寫函數(shù)才真正起到歸一化作用。LTRIM和RTRIM也有一個問題要說明它們只去空格空格字符ASCII 32不去全角空格ASCII 12288也不去掉制表符和換行符。很多人以為它們能去掉所有用戶錄入時產(chǎn)生的空字符實際上遇到從網(wǎng)頁或 Excel 復(fù)制進(jìn)來的數(shù)據(jù)里面?；煊腥强崭?、CHAR(9)、CHAR(10)這時LTRIM/RTRIM不會生效。正確做法是用REPLACE把全角空格替換掉再配合LTRIM/RTRIM。我在處理過一批從舊系統(tǒng)導(dǎo)出的地址數(shù)據(jù)時就遇到過這種情況直接看數(shù)據(jù)完全看不出問題一對比長度才發(fā)現(xiàn)每行都多出了一兩個字符。3. 組合實戰(zhàn)從拼接到拆分再到清洗3.1 用 CONCAT 與 CONCAT_WS 做安全的橫向拼接在 2012 版本之前SQL Server 里拼接字符串常用運(yùn)算符a b得到ab。這個方式的麻煩之處在于只要有一個操作數(shù)是NULL整個結(jié)果就是NULL。很多人為了規(guī)避這個問題每段都要寫ISNULL(column, )既啰嗦又容易漏。從 SQL Server 2012 開始微軟提供了CONCAT函數(shù)。它可以接收多個參數(shù)自動把NULL當(dāng)作空字符串處理不需要ISNULL。比如CONCAT(訂單號, order_no, 金額, amount)即使order_no為NULL結(jié)果也是訂單號金額xxx。這比手動拼加號要穩(wěn)妥很多。但也要注意它不會對參數(shù)做任何類型轉(zhuǎn)換。如果里面混了一個日期類型它會按該類型的默認(rèn)格式轉(zhuǎn)成字符串再拼通常得到的是你不太想要的格式。CONCAT_WS是在CONCAT基礎(chǔ)上加了分隔符參數(shù)第一個參數(shù)是分隔符后面才是要拼接的內(nèi)容。比如CONCAT_WS(-, year, month, day)會自動得到2024-01-05這樣的格式而且不會出現(xiàn)2024--1--5這種因為某段為空導(dǎo)致分隔符連續(xù)出現(xiàn)的情況。這一點(diǎn)在拼接地址、拼接姓名、拼接多段編碼時特別有用。3.2 用 STRING_AGG 做跨行聚合拼接STRING_AGG是 SQL Server 2017 中引入的字符串聚合函數(shù)作用類似于FOR XML PATH的替代方案但它寫起來更直觀、性能也更好。基本用法是STRING_AGG(column, 分隔符)后面可以跟WITHIN GROUP (ORDER BY ...)來控制拼接順序。比如把某部門員工姓名按工號順序拼成一行可以寫成SELECT department_id, STRING_AGG(employee_name, 、) WITHIN GROUP (ORDER BY employee_no) AS name_list FROM employee GROUP BY department_id;這個函數(shù)有兩個明顯的坑。第一拼接結(jié)果默認(rèn)會截斷到NVARCHAR(4000)或VARCHAR(8000)如果你的數(shù)據(jù)量很大結(jié)果會被靜默截斷沒有任何報錯。我曾見過一個報表因為某組明細(xì)太多拼接結(jié)果被截斷導(dǎo)致導(dǎo)出文件里數(shù)據(jù)缺了一部分排查了很久才發(fā)現(xiàn)是這個原因。解決辦法是使用STRING_AGG的重載形式把返回值類型顯式聲明為VARCHAR(MAX)或NVARCHAR(MAX)例如STRING_AGG(CONVERT(NVARCHAR(MAX), employee_name), ,)第二STRING_AGG不會自動去重也不會過濾NULL。如果你想去重得先GROUP BY或使用子查詢把重復(fù)值去掉如果NULL混在里面它會被忽略掉但不參與拼接。這個行為在實際使用中容易被忽略因為結(jié)果看起來正常但數(shù)量對不上。3.3 用 STRING_SPLIT 做字符串拆分以及它的意外產(chǎn)物STRING_SPLIT是 SQL Server 2016 引入的內(nèi)置表值函數(shù)輸入一個字符串和一個分隔符輸出一列名為value的表?；居梅⊿ELECT value FROM STRING_SPLIT(a,b,c, ,);它返回三行a、b、c。這個函數(shù)最常見的應(yīng)用是把前端傳過來的1,2,3類型參數(shù)轉(zhuǎn)換成表再和業(yè)務(wù)表做JOIN。相比以前用循環(huán)、XML 拆分字符串的老辦法STRING_SPLIT簡潔、性能尚可尤其在處理中長字符串時比 XML 方式穩(wěn)定得多。但要注意一個關(guān)鍵限制STRING_SPLIT輸出結(jié)果不保證順序。如果你依賴拆分后每個元素的原始順序必須自己額外處理。微軟官方文檔也明確說了這一點(diǎn)。常見補(bǔ)救方案是在拆分前給每個元素加上序號或者用JSON_OPENJSON配合索引來做。實際上如果你用OPENJSON的默認(rèn)數(shù)組解析方式返回結(jié)果會多出一個[key]列正好可以用來表示順序SELECT [key], value FROM OPENJSON([a,b,c]);這會返回key為 0、1、2value為a、b、c。所以當(dāng)你需要保序拆分時OPENJSON往往比STRING_SPLIT更合適。這是很多人在實際項目中摸索出來的技巧官方文檔里不會特意提醒你。3.4 動態(tài) SQL 拼接字符串函數(shù)的高級應(yīng)用場動態(tài) SQL 是字符串函數(shù)特別能體現(xiàn)價值的地方。你要拼接一個查詢條件里面包含列名、表名或動態(tài)排序字段不能直接用參數(shù)化查詢解決就得手動構(gòu)造 SQL 字符串。這時候QUOTENAME、REPLACE、CHAR(39)、STUFF這些函數(shù)都會派上用場。QUOTENAME是一個被低估的函數(shù)它可以把標(biāo)識符用方括號包裹起來避免表名或列名里包含特殊字符或空格時導(dǎo)致語法錯誤。比如QUOTENAME(order table)返回[order table]。它還能自動轉(zhuǎn)義內(nèi)部的]字符安全性比手動拼接方括號要高。不過要注意QUOTENAME默認(rèn)只處理標(biāo)識符不要拿它來做字符串字面量的轉(zhuǎn)義比如你試圖QUOTENAME(abcdef)并不會得到預(yù)期的效果。拼接字符串字面量時單引號的轉(zhuǎn)義幾乎是每個動態(tài) SQL 開發(fā)者的痛點(diǎn)。比較安全的做法是用CHAR(39)來代替單引號避免多層嵌套時引號計數(shù)混亂。比如SET sql SELECT * FROM t WHERE name CHAR(39) 張三 CHAR(39);這樣讀起來復(fù)雜一些但至少不會因為少寫一個引號而報語法錯誤。再配合CHAR(13) CHAR(10)來換行生成的 SQL 在調(diào)試時也更易于閱讀。4. 避坑指南與排查技巧4.1 NULL 傳播與空字符串陷阱字符串函數(shù)處理NULL時有一個共同特點(diǎn)輸入NULL輸出NULL。LEN(NULL)返回NULLSUBSTRING(NULL, 1, 1)返回NULLREPLACE(NULL, a, b)返回NULL。這在聚合查詢里尤其危險因為NULL值會悄悄地讓整個表達(dá)式的計算結(jié)果變成NULL而你看到的報表卻是空白而不是報錯。所以請養(yǎng)成一個習(xí)慣在寫字符串表達(dá)式之前先想清楚該字段是否可能為NULL。如果可能優(yōu)先用COALESCE或ISNULL包一層默認(rèn)值。但也不要盲目全部加ISNULL因為如果某個字段本來就應(yīng)該保留NULL的語義比如“備注為空”和“備注為 NULL”在業(yè)務(wù)上含義不同硬把它轉(zhuǎn)成空字符串反而會讓下游無法區(qū)分真實場景。另外一個容易忽略的地方是空字符串和NULL的比較。SQL Server 里空字符串不是NULL但兩者在JOIN或WHERE中的表現(xiàn)完全不同。比如WHERE column 不會匹配NULL。如果你用LTRIM(RTRIM(column))去清洗數(shù)據(jù)請確?？兆址脖划?dāng)成“無意義值”過濾掉不然統(tǒng)計結(jié)果會比預(yù)期大。4.2 排序規(guī)則對字符串函數(shù)的影響排序規(guī)則決定字符串比較和排序的規(guī)則但它也會間接影響一些函數(shù)的行為。最典型的例子是CHARINDEX在大小寫敏感和不敏感的排序規(guī)則下返回結(jié)果可能不同。比如CHARINDEX(A, abc)在_CI_AS大小寫不敏感排序規(guī)則下返回 1而在_CS_AS大小寫敏感下返回 0因為A和a不匹配。這帶來的實際問題是同一套代碼在開發(fā)庫和測試庫上運(yùn)行結(jié)果一樣但部署到生產(chǎn)庫后結(jié)果卻不同原因是這個數(shù)據(jù)庫的排序規(guī)則設(shè)置不一樣。排查這類問題不能只盯著函數(shù)語句本身還要看數(shù)據(jù)庫或列的排序規(guī)則。另外中文環(huán)境下最常見的排序規(guī)則是Chinese_PRC_CI_AS它對拼音和筆畫也有自己的一套規(guī)則這會影響ORDER BY中字符串的排序結(jié)果。如果你遇到按照拼音排序但實際結(jié)果卻讓人感覺像是按筆畫排序很可能就是默認(rèn)排序規(guī)則在起作用。這時想讓排序結(jié)果更可控就需要在ORDER BY子句里顯式指定COLLATE比如ORDER BY name COLLATE Chinese_PRC_CS_AS_KS_WS。4.3 類型轉(zhuǎn)換與隱式轉(zhuǎn)換的隱性開銷字符串函數(shù)和類型轉(zhuǎn)換是密不可分的。有時你拿一個VARCHAR列和一個NVARCHAR變量做比較SQL Server 會隱式把VARCHAR轉(zhuǎn)換成NVARCHAR這個過程可能讓索引失效還會改變字符串函數(shù)的處理行為。LEN和SUBSTRING對VARCHAR和NVARCHAR的字符數(shù)計算結(jié)果基本相同但DATALENGTH完全不同這是最容易踩的坑。我遇到過這樣一個案例某表的主鍵列是VARCHAR(20)另一個關(guān)聯(lián)表用的是NVARCHAR(20)兩個表做JOIN時因為隱式轉(zhuǎn)換導(dǎo)致查詢走了全表掃描數(shù)據(jù)量上來后查詢延遲嚴(yán)重。解決辦法是統(tǒng)一兩邊列的數(shù)據(jù)類型或者在關(guān)聯(lián)時顯式CONVERT讓執(zhí)行計劃能有效使用索引。這個教訓(xùn)放在字符串函數(shù)場景里是一樣的如果你頻繁對某列使用函數(shù)比如WHERE UPPER(name) ABC那么在大多數(shù)情況下索引就無法正常使用因為對列的計算發(fā)生在索引查找之前。如果這個查詢是高頻查詢更好的方式是預(yù)先存儲規(guī)范化的值而不是每次查詢都做轉(zhuǎn)換。4.4 常見問題速查表癥狀、原因與解法下面整理一些我在實際項目里經(jīng)常遇到的典型問題做成速查表方便你對照排查。癥狀常見原因解決方案查詢結(jié)果包含多余的前后空白去掉空格后才發(fā)現(xiàn)數(shù)據(jù)重復(fù)錄入端未做LTRIM/RTRIM或包含了全角空格、制表符、換行符使用REPLACE先替換全角空格和特殊字符再統(tǒng)一去空格考慮錄入時提前清洗CHARINDEX找不到子串但肉眼可見字符串里有子串中包含不可見字符如換行或制表符用DATALENGTH比較字符長度定位差異把隱性字符先替換掉用STRING_SPLIT拆分后順序錯了STRING_SPLIT不保證輸出順序改用OPENJSON獲取[key]列或拆分時附帶序數(shù)列拼接結(jié)果變短懷疑數(shù)據(jù)丟失STRING_AGG結(jié)果被截斷到 4000/8000 字符顯式轉(zhuǎn)換為NVARCHAR(MAX)或VARCHAR(MAX)拼接多字段時出現(xiàn)大量“空”內(nèi)容但看不出哪里有問題NULL被當(dāng)作空字符串處理或隱式轉(zhuǎn)換改變了內(nèi)容檢查ISNULL/COALESCE覆蓋范圍排查是否有NULL語義被誤改在數(shù)據(jù)庫查的字符串長度與應(yīng)用層查的長度不一致應(yīng)用層按字節(jié)數(shù)SQL 層按字符數(shù)尤其是中文編碼明確使用DATALENGTH和LEN的語義根據(jù)需求選擇某個字符串函數(shù)在開發(fā)環(huán)境正常生產(chǎn)環(huán)境異常數(shù)據(jù)庫排序規(guī)則或默認(rèn)語言設(shè)置不同比較兩庫COLLATION設(shè)置必要時在語句中顯式指定排序規(guī)則5. 性能優(yōu)化與替代方案5.1 什么時候該在 SQL 里做什么時候該在應(yīng)用層做字符串函數(shù)雖然提供了很大的靈活度但并不意味著所有處理都應(yīng)該塞到 SQL 里。字符串處理往往涉及大量的 CPU 計算尤其當(dāng)數(shù)據(jù)量達(dá)到百萬級時對每一行都做復(fù)雜的字符串拆分、正則替代或多次嵌套函數(shù)代價是很高的。在這種情況下合理做法是在數(shù)據(jù)庫里做最小必要操作把更復(fù)雜的業(yè)務(wù)邏輯放到應(yīng)用層。我個人的判斷標(biāo)準(zhǔn)是如果字符串處理只影響展示層不參與查詢條件、聚合邏輯或JOIN條件那優(yōu)先在應(yīng)用層輸出如果處理結(jié)果用于WHERE過濾、分組聚合或報表統(tǒng)計那就必須在 SQL 里完成因為你要利用數(shù)據(jù)庫的索引和聚合能力。比如要把一段日志里的時間戳拆出來做過濾那這個拆分邏輯必須在 SQL 里做但如果只是把查詢結(jié)果里的日期格式換一種顯示方式那完全可以在前端做。另外注意不要做冗余的字符串函數(shù)調(diào)用。比如你已經(jīng)用了SUBSTRING(column, 1, 10)再對它整體UPPER這沒問題。但如果你在WHERE里對同一列同時用UPPER和LTRIM而且該列本身已經(jīng)被前面邏輯規(guī)范化過那重復(fù)計算是白費(fèi)力氣的。養(yǎng)成看執(zhí)行計劃的好習(xí)慣特別是那些在輸出列里出現(xiàn)大量標(biāo)量函數(shù)的查詢你可能會驚訝于 CPU 消耗集中在幾個不起眼的函數(shù)上。5.2 避免在 WHERE 子句中對列使用函數(shù)這是一個老生常談的優(yōu)化原則不要在WHERE子句中對列應(yīng)用函數(shù)除非你已經(jīng)仔細(xì)評估過索引的影響。比如WHERE SUBSTRING(name, 1, 1) 張可能無法有效利用name列上的索引而WHERE name LIKE 張%則可以通過索引前綴匹配大幅減少掃描范圍。很多時候改寫查詢邏輯就能讓同一個業(yè)務(wù)需求獲得更好的性能。同樣的道理也適用于CHARINDEX。如果你經(jīng)常需要查某個子串是否出現(xiàn)在某列中比如WHERE CHARINDEX(VIP, remark) 0當(dāng)數(shù)據(jù)量大時這個查詢必然走全表掃描因為每個remark都要被掃描一遍。一個可行的替代方案是增加一個“是否包含 VIP”的持久化列由應(yīng)用層在寫入時維護(hù)或者在插入時通過觸發(fā)器維護(hù)。雖然這樣做犧牲了一些實時性但查詢性能會大幅改善。5.3 用 XML 與 JSON 拆分字符串的比較在STRING_SPLIT出現(xiàn)之前拆分字符串的主流方案是 XML。常見寫法是把字符串替換成 XML 節(jié)點(diǎn)再使用nodes()方法提取。比如DECLARE str NVARCHAR(100) Na,b,c; SELECT value FROM ( SELECT CAST(x REPLACE(str, ,, /xx) /x AS XML) AS xml_data ) t CROSS APPLY xml_data.nodes(/x) q(value);這個方式的弊端很明顯如果字符串中包含合法的 XML 特殊字符比如、、就會導(dǎo)致轉(zhuǎn)換失敗或數(shù)據(jù)錯亂。雖然可以通過FOR XML PATH轉(zhuǎn)義來規(guī)避但代碼復(fù)雜度一下子高了很多。另一個選擇是OPENJSON它能把字符串字符串解析成 JSON 數(shù)組。只要把字符串改成 JSON 格式比如[a,b,c]它就能返回一個表并帶有key、value等列。這個方法比 XML 安全且自帶了序號功能是保序拆分的首選。唯一的要求是 SQL Server 2016 及以上版本這幾乎已經(jīng)是現(xiàn)代數(shù)據(jù)庫的最低版本了所以可以放心使用。6. 個人實操心得把字符串函數(shù)用得順手的一些小技巧繼續(xù)分享幾個我平時用得很多的小技巧。其中一個是用STRING_AGG配合DISTINCT實現(xiàn)去重拼接。雖然STRING_AGG本身不支持直接DISTINCT但你可以先把去重后的數(shù)據(jù)放到子查詢或 CTE 里再對子查詢結(jié)果做聚合。比如WITH distinct_names AS ( SELECT DISTINCT department_id, employee_name FROM employee ) SELECT department_id, STRING_AGG(employee_name, 、) FROM distinct_names GROUP BY department_id;這個寫法在生成某些維度匯總表時非常方便既不需要GROUP_CONCAT因為 SQL Server 沒有這個函數(shù)也不需要FOR XML PATH這種繞彎寫法。另一個技巧是用FORMAT函數(shù)來格式化字符串比如電話號碼、日期和貨幣。不過要提醒一句FORMAT的性能開銷比較大它依賴于 .NET 運(yùn)行時。在數(shù)據(jù)量小、展示用的時候沒問題但如果你在幾百萬行結(jié)果里用FORMAT性能會讓你懷疑人生。這種場景更適合直接在應(yīng)用層格式化或者用CONVERT的樣式參數(shù)做有限格式化。最后一個想說的習(xí)慣是每個字符串處理腳本都該配上注釋尤其當(dāng)它涉及多個CHARINDEX、SUBSTRING嵌套時。你寫的時候很明白但過三個月再回來看可能就需要重新推導(dǎo)一遍邏輯。我會在關(guān)鍵位置寫清楚“當(dāng)前這一步是為了提取出訂單號里的日期部分”或“這里的三層REPLACE分別處理全角空格、普通空格和換行符”。這些注釋不是為了別人而是為了三個月后的自己。字符串函數(shù)看起來簡單實際用得好與不好會在查詢可讀性、性能和穩(wěn)定性上有很大差別。把每個函數(shù)的行為邊界理解清楚再結(jié)合業(yè)務(wù)場景做合理組合寫出來的 SQL 不僅能跑通還能經(jīng)得起數(shù)據(jù)量和業(yè)務(wù)變化的考驗。