不少人一开始接触 SQL Server 里的字符串函数都是从LEN、SUBSTRING这类单个函数用起等真正碰到复杂一点的报表、ETL、接口数据清洗时才发现这些零散函数怎么拼都不顺手。第 41 章笔记我就打算换个思路不按函数名罗列一遍而是把字符串函数当成一组可以组合使用的工具箱按实际需求来讲它们各自扮演什么角色、有哪些坑、怎样搭配才能组合出自己想要的效果。这篇内容适合刚把 T-SQL 基础语法过完、准备上手写真实查询的人也适合写了几年 SQL 但遇到字符串处理还是习惯去网上临时搜函数的开发者把常用函数的行为边界和内部逻辑摸清楚了很多代码其实不用反复试错。1. 字符串函数全景与使用场景1.1 先理清 SQL Server 里的字符串处理生态SQL Server 中的字符串函数分布在几个不同的类别里很多人容易混淆。一类是纯标量函数也就是输入一个或几个字符串输出一个新字符串或一个数值比如LEN、UPPER、SUBSTRING这类。另一类是较新的表值函数最典型的就是STRING_SPLIT它把一串文本拆成一张表可以和JOIN、IN配合使用。还有一类属于聚合函数典型代表是STRING_AGG它把多行的值合并成单个字符串。这几类的使用场景完全不同把它们混在一起记遇到实际问题时就会容易犯方向性错误。举个例子你要把某个订单表里的多个商品名称拼到一个单元格里如果只知道CONCAT就麻烦了。CONCAT是行内的拼接处理不了多行合并STRING_AGG才是做跨行拼接的。反过来你想判断某个字符串里是否包含某个子串SUBSTRING是做截取CHARINDEX才是查位置。搞清楚“这个函数解决的是哪个维度的问题”比死记函数签名更重要。1.2 从业务视角看字符串函数的价值字符串处理在业务里并不是花哨炫技而是每天都在用的基础能力。比如客户姓名清洗用户录入时可能带了前后空格、中间夹杂全角空格或者大小写不统一再比如接口对接时拿到的原始报文里日期格式是2024-01-05T10:30:00Z要在 SQL 里转成标准日期就必须先用字符串函数把T和Z处理掉还有报表开发时要把几个维度的文本拼接成展示列或者把明细数据按某个分组拼成一行。这类工作如果全靠应用程序代码做一是浪费网络带宽二是处理逻辑分散在业务系统里排查问题困难。在 SQL 层直接用字符串函数处理速度快、逻辑集中、可维护性也更高。从这个角度看字符串函数不是“会不会用”的问题而是“能不能用得好”的问题。很多性能问题之所以出现往往不是某个函数本身慢而是因为没有理解函数的内部运作方式导致代码写得绕、扫描次数多、隐式转换频繁。2. 核心函数拆解从返回值到边界行为2.1 测量类函数LEN 与 DATALENGTH 的区别先说最容易混淆的一对LEN和DATALENGTH。LEN返回字符串的字符数而且它会自动去掉结尾的空格DATALENGTH返回字节数它不会去空格。这两个函数的结果只在一种情况下完全一致就是字符串里不包含中文字符并且结尾没有空格的时候。实际工作中最常踩的坑是下面这种表中某个字段是NVARCHAR(50)你查询WHERE LEN(column) 6以为过滤的是“6个字符”的数据但如果字段里存了类似“测试 “这样带尾随空格的值实际字符数只有 2。由于LEN忽略尾部空格你可能把本应匹配的值漏掉了。反过来如果用了DATALENGTH(column) 12因为中文在NCHAR/NVARCHAR里占 2 字节一个“数据库”三个字是 6 字节而非 3 字节所以数值判断会完全不同。我自己的习惯是需要判断“是否为空串”或者“是否超过某长度”时优先用DATALENGTH配合去除空格后的结果而不是单用LEN因为默认的LEN行为在数据质量参差不齐的表中会产生误导。不过要提醒一点DATALENGTH对NULL也返回NULL所以使用时通常先用ISNULL或COALESCE做保护。2.2 查找类函数CHARINDEX、PATINDEX 与字符串定位CHARINDEX是查找子串位置的函数它返回的是子串在字符串中第一次出现的起始位置如果找不到就返回 0。比如CHARINDEX(, testexample.com)返回 5。第三个参数可以指定从第几位开始查找这在处理一个字符串里出现多次相同分隔符时非常有用。PATINDEX跟CHARINDEX的写法不同它支持模式匹配类似于LIKE中的通配符。比如你在一堆地址里找“数字路”这种模式可以写成PATINDEX(%[0-9]路%, address)。它返回的是符合模式的起始位置不是布尔值。这在解析不规则文本时非常有用但也容易犯一个错误写PATINDEX时忘了模式前后的%。没加%意味着必须从字符串开头精确匹配到结尾绝大多数情况下你会得到 0然后怀疑数据有问题。另外一个常见误区是把CHARINDEX的位置和字符串下标搞混。SQL Server 的字符串位置是从 1 开始的不是从 0 开始这和 C#、Java 的substring行为完全不同。所以当你在应用层已经习惯了索引从 0 开始时到了 SQL 里很容易多减一次 1导致取出来的字符差一位。我之前排查过一个提取订单号后四位的问题最终定位到就是位置参数差了 1结果整批数据都错位了。2.3 提取类函数SUBSTRING、LEFT、RIGHT 的正确姿势SUBSTRING是最常用的提取函数三个参数分别是原始字符串、起始位置、提取长度。它的行为有几个特点需要注意。第一如果起始位置超过了字符串长度返回结果是空字符串而不是报错。第二如果起始位置加上提取长度超过了字符串总长度它只返回到末尾的字符不报错。这个特性在数据长度不够时非常实用但也容易掩盖问题让你误以为数据没问题。LEFT和RIGHT更像是SUBSTRING的语法糖LEFT(abc, 2)等价于SUBSTRING(abc, 1, 2)RIGHT(abc, 2)等价于SUBSTRING(abc, LEN(abc) - 1, 2)。但要注意LEFT和RIGHT在处理中文时和SUBSTRING一样都是按字符而不是字节来截取的。有人会误以为它们按字节截取于是拿它处理二进制或混有全角符号的字符串结果得到乱码。如果你处理的字段里有表情符号或特殊 Unicode 字符情况会更复杂因为 SQL Server 的默认排序规则可能把某些字符看作多个字节单纯用LEFT有可能把代理对拆开。提取类函数在数据清洗中的经典用法包括从身份证号里提取出生日期、从订单号里提取地区码、从邮箱地址里提取用户名或域名。这些场景的关键点在于先用CHARINDEX定位分隔符再按位置截取而不是硬编码固定长度。因为硬编码长度在数据格式变化时很容易失控。2.4 改写类函数REPLACE、STUFF 与正则的边界REPLACE很好理解把字符串中的某段内容替换成新内容。但有一点需要注意REPLACE不是只替换第一次出现的匹配也不是用正则表达式而是把整个字符串中所有匹配的子串全部替换。想要只替换第一个出现的匹配SQL Server 没有内置的直接函数常规做法是用STUFF配合CHARINDEX自己实现。STUFF是很多开发者不太熟悉的函数它做的事情是从指定位置删除指定长度的字符然后插入一个新的字符串。比如STUFF(abcdef, 2, 2, XY)的结果是aXYdef。这个函数在拼接动态 SQL、修正固定格式文本时非常有用。举个例子你要把订单号中间几位替换成星号用STUFF就很干净STUFF(order_no, 4, 4, ****)。用REPLACE反而做不到这种按位置替换的效果。有些人会问REPLACE能不能实现正则替换答案是不能。SQL Server 原生函数里没有正则替换。如果你真的需要按正则匹配替换内容一般的选择是使用CLR聚合函数或者调用OLE Automation存储过程但这两者都不是默认推荐方案尤其在生产环境里限制多、风险大。更常见、更稳妥的做法是用PATINDEX定位模式再用STUFF多次替换。虽然代码会长一些但至少纯 T-SQL 就能完成不需要额外部署组件。2.5 转换与规范化UPPER、LOWER、LTRIM、RTRIM 的隐藏作用大小写转换和去空格函数看起来简单但在实际数据清洗中作用很大。UPPER和LOWER用于把数据转换成统一大小写常见于用户名或邮箱匹配场景。注意一点SQL Server 的排序规则决定了大写字母和对应小写字母的默认比较行为。如果数据库排序规则是Chinese_PRC_CI_AS默认是大小写不敏感_CI表示 case-insensitive所以WHERE name ABC和WHERE name abc在没有强制排序规则时的结果一样。此时你再费劲去UPPER列值可能白白增加了计算开销却没有实际意义。只有在排序规则区分大小写如_CS_AS时大小写函数才真正起到归一化作用。LTRIM和RTRIM也有一个问题要说明它们只去空格空格字符ASCII 32不去全角空格ASCII 12288也不去掉制表符和换行符。很多人以为它们能去掉所有用户录入时产生的空字符实际上遇到从网页或 Excel 复制进来的数据里面常混有全角空格、CHAR(9)、CHAR(10)这时LTRIM/RTRIM不会生效。正确做法是用REPLACE把全角空格替换掉再配合LTRIM/RTRIM。我在处理过一批从旧系统导出的地址数据时就遇到过这种情况直接看数据完全看不出问题一对比长度才发现每行都多出了一两个字符。3. 组合实战从拼接到拆分再到清洗3.1 用 CONCAT 与 CONCAT_WS 做安全的横向拼接在 2012 版本之前SQL Server 里拼接字符串常用运算符a b得到ab。这个方式的麻烦之处在于只要有一个操作数是NULL整个结果就是NULL。很多人为了规避这个问题每段都要写ISNULL(column, )既啰嗦又容易漏。从 SQL Server 2012 开始微软提供了CONCAT函数。它可以接收多个参数自动把NULL当作空字符串处理不需要ISNULL。比如CONCAT(订单号, order_no, 金额, amount)即使order_no为NULL结果也是订单号金额xxx。这比手动拼加号要稳妥很多。但也要注意它不会对参数做任何类型转换。如果里面混了一个日期类型它会按该类型的默认格式转成字符串再拼通常得到的是你不太想要的格式。CONCAT_WS是在CONCAT基础上加了分隔符参数第一个参数是分隔符后面才是要拼接的内容。比如CONCAT_WS(-, year, month, day)会自动得到2024-01-05这样的格式而且不会出现2024--1--5这种因为某段为空导致分隔符连续出现的情况。这一点在拼接地址、拼接姓名、拼接多段编码时特别有用。3.2 用 STRING_AGG 做跨行聚合拼接STRING_AGG是 SQL Server 2017 中引入的字符串聚合函数作用类似于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;这个函数有两个明显的坑。第一拼接结果默认会截断到NVARCHAR(4000)或VARCHAR(8000)如果你的数据量很大结果会被静默截断没有任何报错。我曾见过一个报表因为某组明细太多拼接结果被截断导致导出文件里数据缺了一部分排查了很久才发现是这个原因。解决办法是使用STRING_AGG的重载形式把返回值类型显式声明为VARCHAR(MAX)或NVARCHAR(MAX)例如STRING_AGG(CONVERT(NVARCHAR(MAX), employee_name), ,)第二STRING_AGG不会自动去重也不会过滤NULL。如果你想去重得先GROUP BY或使用子查询把重复值去掉如果NULL混在里面它会被忽略掉但不参与拼接。这个行为在实际使用中容易被忽略因为结果看起来正常但数量对不上。3.3 用 STRING_SPLIT 做字符串拆分以及它的意外产物STRING_SPLIT是 SQL Server 2016 引入的内置表值函数输入一个字符串和一个分隔符输出一列名为value的表。基本用法SELECT value FROM STRING_SPLIT(a,b,c, ,);它返回三行a、b、c。这个函数最常见的应用是把前端传过来的1,2,3类型参数转换成表再和业务表做JOIN。相比以前用循环、XML 拆分字符串的老办法STRING_SPLIT简洁、性能尚可尤其在处理中长字符串时比 XML 方式稳定得多。但要注意一个关键限制STRING_SPLIT输出结果不保证顺序。如果你依赖拆分后每个元素的原始顺序必须自己额外处理。微软官方文档也明确说了这一点。常见补救方案是在拆分前给每个元素加上序号或者用JSON_OPENJSON配合索引来做。实际上如果你用OPENJSON的默认数组解析方式返回结果会多出一个[key]列正好可以用来表示顺序SELECT [key], value FROM OPENJSON([a,b,c]);这会返回key为 0、1、2value为a、b、c。所以当你需要保序拆分时OPENJSON往往比STRING_SPLIT更合适。这是很多人在实际项目中摸索出来的技巧官方文档里不会特意提醒你。3.4 动态 SQL 拼接字符串函数的高级应用场动态 SQL 是字符串函数特别能体现价值的地方。你要拼接一个查询条件里面包含列名、表名或动态排序字段不能直接用参数化查询解决就得手动构造 SQL 字符串。这时候QUOTENAME、REPLACE、CHAR(39)、STUFF这些函数都会派上用场。QUOTENAME是一个被低估的函数它可以把标识符用方括号包裹起来避免表名或列名里包含特殊字符或空格时导致语法错误。比如QUOTENAME(order table)返回[order table]。它还能自动转义内部的]字符安全性比手动拼接方括号要高。不过要注意QUOTENAME默认只处理标识符不要拿它来做字符串字面量的转义比如你试图QUOTENAME(abcdef)并不会得到预期的效果。拼接字符串字面量时单引号的转义几乎是每个动态 SQL 开发者的痛点。比较安全的做法是用CHAR(39)来代替单引号避免多层嵌套时引号计数混乱。比如SET sql SELECT * FROM t WHERE name CHAR(39) 张三 CHAR(39);这样读起来复杂一些但至少不会因为少写一个引号而报语法错误。再配合CHAR(13) CHAR(10)来换行生成的 SQL 在调试时也更易于阅读。4. 避坑指南与排查技巧4.1 NULL 传播与空字符串陷阱字符串函数处理NULL时有一个共同特点输入NULL输出NULL。LEN(NULL)返回NULLSUBSTRING(NULL, 1, 1)返回NULLREPLACE(NULL, a, b)返回NULL。这在聚合查询里尤其危险因为NULL值会悄悄地让整个表达式的计算结果变成NULL而你看到的报表却是空白而不是报错。所以请养成一个习惯在写字符串表达式之前先想清楚该字段是否可能为NULL。如果可能优先用COALESCE或ISNULL包一层默认值。但也不要盲目全部加ISNULL因为如果某个字段本来就应该保留NULL的语义比如“备注为空”和“备注为 NULL”在业务上含义不同硬把它转成空字符串反而会让下游无法区分真实场景。另外一个容易忽略的地方是空字符串和NULL的比较。SQL Server 里空字符串不是NULL但两者在JOIN或WHERE中的表现完全不同。比如WHERE column 不会匹配NULL。如果你用LTRIM(RTRIM(column))去清洗数据请确保空字符串也被当成“无意义值”过滤掉不然统计结果会比预期大。4.2 排序规则对字符串函数的影响排序规则决定字符串比较和排序的规则但它也会间接影响一些函数的行为。最典型的例子是CHARINDEX在大小写敏感和不敏感的排序规则下返回结果可能不同。比如CHARINDEX(A, abc)在_CI_AS大小写不敏感排序规则下返回 1而在_CS_AS大小写敏感下返回 0因为A和a不匹配。这带来的实际问题是同一套代码在开发库和测试库上运行结果一样但部署到生产库后结果却不同原因是这个数据库的排序规则设置不一样。排查这类问题不能只盯着函数语句本身还要看数据库或列的排序规则。另外中文环境下最常见的排序规则是Chinese_PRC_CI_AS它对拼音和笔画也有自己的一套规则这会影响ORDER BY中字符串的排序结果。如果你遇到按照拼音排序但实际结果却让人感觉像是按笔画排序很可能就是默认排序规则在起作用。这时想让排序结果更可控就需要在ORDER BY子句里显式指定COLLATE比如ORDER BY name COLLATE Chinese_PRC_CS_AS_KS_WS。4.3 类型转换与隐式转换的隐性开销字符串函数和类型转换是密不可分的。有时你拿一个VARCHAR列和一个NVARCHAR变量做比较SQL Server 会隐式把VARCHAR转换成NVARCHAR这个过程可能让索引失效还会改变字符串函数的处理行为。LEN和SUBSTRING对VARCHAR和NVARCHAR的字符数计算结果基本相同但DATALENGTH完全不同这是最容易踩的坑。我遇到过这样一个案例某表的主键列是VARCHAR(20)另一个关联表用的是NVARCHAR(20)两个表做JOIN时因为隐式转换导致查询走了全表扫描数据量上来后查询延迟严重。解决办法是统一两边列的数据类型或者在关联时显式CONVERT让执行计划能有效使用索引。这个教训放在字符串函数场景里是一样的如果你频繁对某列使用函数比如WHERE UPPER(name) ABC那么在大多数情况下索引就无法正常使用因为对列的计算发生在索引查找之前。如果这个查询是高频查询更好的方式是预先存储规范化的值而不是每次查询都做转换。4.4 常见问题速查表症状、原因与解法下面整理一些我在实际项目里经常遇到的典型问题做成速查表方便你对照排查。症状常见原因解决方案查询结果包含多余的前后空白去掉空格后才发现数据重复录入端未做LTRIM/RTRIM或包含了全角空格、制表符、换行符使用REPLACE先替换全角空格和特殊字符再统一去空格考虑录入时提前清洗CHARINDEX找不到子串但肉眼可见字符串里有子串中包含不可见字符如换行或制表符用DATALENGTH比较字符长度定位差异把隐性字符先替换掉用STRING_SPLIT拆分后顺序错了STRING_SPLIT不保证输出顺序改用OPENJSON获取[key]列或拆分时附带序数列拼接结果变短怀疑数据丢失STRING_AGG结果被截断到 4000/8000 字符显式转换为NVARCHAR(MAX)或VARCHAR(MAX)拼接多字段时出现大量“空”内容但看不出哪里有问题NULL被当作空字符串处理或隐式转换改变了内容检查ISNULL/COALESCE覆盖范围排查是否有NULL语义被误改在数据库查的字符串长度与应用层查的长度不一致应用层按字节数SQL 层按字符数尤其是中文编码明确使用DATALENGTH和LEN的语义根据需求选择某个字符串函数在开发环境正常生产环境异常数据库排序规则或默认语言设置不同比较两库COLLATION设置必要时在语句中显式指定排序规则5. 性能优化与替代方案5.1 什么时候该在 SQL 里做什么时候该在应用层做字符串函数虽然提供了很大的灵活度但并不意味着所有处理都应该塞到 SQL 里。字符串处理往往涉及大量的 CPU 计算尤其当数据量达到百万级时对每一行都做复杂的字符串拆分、正则替代或多次嵌套函数代价是很高的。在这种情况下合理做法是在数据库里做最小必要操作把更复杂的业务逻辑放到应用层。我个人的判断标准是如果字符串处理只影响展示层不参与查询条件、聚合逻辑或JOIN条件那优先在应用层输出如果处理结果用于WHERE过滤、分组聚合或报表统计那就必须在 SQL 里完成因为你要利用数据库的索引和聚合能力。比如要把一段日志里的时间戳拆出来做过滤那这个拆分逻辑必须在 SQL 里做但如果只是把查询结果里的日期格式换一种显示方式那完全可以在前端做。另外注意不要做冗余的字符串函数调用。比如你已经用了SUBSTRING(column, 1, 10)再对它整体UPPER这没问题。但如果你在WHERE里对同一列同时用UPPER和LTRIM而且该列本身已经被前面逻辑规范化过那重复计算是白费力气的。养成看执行计划的好习惯特别是那些在输出列里出现大量标量函数的查询你可能会惊讶于 CPU 消耗集中在几个不起眼的函数上。5.2 避免在 WHERE 子句中对列使用函数这是一个老生常谈的优化原则不要在WHERE子句中对列应用函数除非你已经仔细评估过索引的影响。比如WHERE SUBSTRING(name, 1, 1) 张可能无法有效利用name列上的索引而WHERE name LIKE 张%则可以通过索引前缀匹配大幅减少扫描范围。很多时候改写查询逻辑就能让同一个业务需求获得更好的性能。同样的道理也适用于CHARINDEX。如果你经常需要查某个子串是否出现在某列中比如WHERE CHARINDEX(VIP, remark) 0当数据量大时这个查询必然走全表扫描因为每个remark都要被扫描一遍。一个可行的替代方案是增加一个“是否包含 VIP”的持久化列由应用层在写入时维护或者在插入时通过触发器维护。虽然这样做牺牲了一些实时性但查询性能会大幅改善。5.3 用 XML 与 JSON 拆分字符串的比较在STRING_SPLIT出现之前拆分字符串的主流方案是 XML。常见写法是把字符串替换成 XML 节点再使用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 特殊字符比如、、就会导致转换失败或数据错乱。虽然可以通过FOR XML PATH转义来规避但代码复杂度一下子高了很多。另一个选择是OPENJSON它能把字符串字符串解析成 JSON 数组。只要把字符串改成 JSON 格式比如[a,b,c]它就能返回一个表并带有key、value等列。这个方法比 XML 安全且自带了序号功能是保序拆分的首选。唯一的要求是 SQL Server 2016 及以上版本这几乎已经是现代数据库的最低版本了所以可以放心使用。6. 个人实操心得把字符串函数用得顺手的一些小技巧继续分享几个我平时用得很多的小技巧。其中一个是用STRING_AGG配合DISTINCT实现去重拼接。虽然STRING_AGG本身不支持直接DISTINCT但你可以先把去重后的数据放到子查询或 CTE 里再对子查询结果做聚合。比如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 没有这个函数也不需要FOR XML PATH这种绕弯写法。另一个技巧是用FORMAT函数来格式化字符串比如电话号码、日期和货币。不过要提醒一句FORMAT的性能开销比较大它依赖于 .NET 运行时。在数据量小、展示用的时候没问题但如果你在几百万行结果里用FORMAT性能会让你怀疑人生。这种场景更适合直接在应用层格式化或者用CONVERT的样式参数做有限格式化。最后一个想说的习惯是每个字符串处理脚本都该配上注释尤其当它涉及多个CHARINDEX、SUBSTRING嵌套时。你写的时候很明白但过三个月再回来看可能就需要重新推导一遍逻辑。我会在关键位置写清楚“当前这一步是为了提取出订单号里的日期部分”或“这里的三层REPLACE分别处理全角空格、普通空格和换行符”。这些注释不是为了别人而是为了三个月后的自己。字符串函数看起来简单实际用得好与不好会在查询可读性、性能和稳定性上有很大差别。把每个函数的行为边界理解清楚再结合业务场景做合理组合写出来的 SQL 不仅能跑通还能经得起数据量和业务变化的考验。