SQL Server之深入理解STUFF
SQL Server之深入理解STUFF
前言
最近项目无论查询报表还是其他数据都在和SQL Server数据库打交道,对于STUFF也有了解,但是发现当下一次再写SQL语句时我还得查看相关具体用法,说到底还是没有完全理解其原理,所以本节我们来谈谈STUFF,Jeff是在项目中哪里不熟悉,哪里不会或者哪里耗时比较多就会去深入理解和巩固即使是很基础的知识,直到完全不用浪费时间去查阅相关资料,这是我的出发点。
深入理解STUFF
STUFF字符串函数是将字符串插入到另一个字符串中。它会删除开始位置第一个字符串中的指定长度的字符,然后将第二个字符串插入到开始位置的第一个字符串中,语法如下。
STUFF(<character_expression>,<开始>,<长度>,<character_expression>)
<character_expression>参数是给定的字符串数据,可以是字符或二进制数据的常量,变量或列。<start>参数是一个整数值,指定开始删除和插入的位置,可以是BIGINT类型。如果<开始>或<长度>参数为负数,则返回NULL字符串。如果<start>参数比第一个<character_expression>长,则返回一个NULL字符串。 <length>参数可以是BIGINT类型,它是一个整数,指定要删除的字符数。如果<length>比第一个<character_expression>长,则删除发生到最后一个<character_expression>中的最后一个字符。
DECLARE @FullName VARCHAR(100) DECLARE @Alias VARCHAR(20) SET @FullName = 'Jeffcky Wang' SET @Alias = ' "Superman" ' SELECT STUFF(@FullName, CHARINDEX(' ', @FullName), 1, @Alias) AS [FullName]
如上STUFF函数中的第一个参数我们给定的是@FullName,第二个是开始的位置,我们通过CHARINDEX函数找出@FullName以空格隔开的的位置返回,最后由@Alias来代替,结果如图所示。
DECLARE @Time VARCHAR(10) SET @Time = '1030' SELECT STUFF(@Time, 3, 0, ':') AS [HH:MM]
我们给定的字符串为@Time即1030,我们从第3个位置开始,删除长度为0,此时则在3前面插入冒号,结果如上图输出10:30。
DECLARE @CreditCardNumber VARCHAR(20) SET @CreditCardNumber = '370200199408103544' SELECT STUFF(@CreditCardNumber, LEN(@CreditCardNumber) -3, 4, 'XXXX') AS [Output]
如上我们将身份证通过STUFF将最后四位用XXXX代替。以上是STUFF最基础的用法。STUFF最常见的用途莫过于结合FOR XML PATH对返回JSON字符串的拼接。首先利用FOR XML PATH则返回XML格式的字符串,我们将FOR XML PATH添加到查询的末尾,此时允许我们将查询的结果作为XML元素输出,元素名称包含在PATH参数中。。
SELECT TOP 5 ',' + Name FROM Production.Product FOR XML PATH ('')
,Adjustable Race,All-Purpose Bike Stand,AWC Logo Cap,BB Ball Bearing,Bearing Ball
此时我们利用STUFF将上述利用FOR XML PATH生成的字符串中的前置逗号去掉,如下:
SELECT Name = STUFF(( SELECT TOP 5 ',' + NAME FROM Production.Product FOR XML PATH('') ), 1, 1, '')
比如我们要查询各种产品中的产品列表名称,最后我们改造成如下:
SELECT TOP 5 p2.ProductID, Name = STUFF(( SELECT ',' + NAME FROM Production.Product AS p1 WHERE p1.ProductID = p2.ProductID FOR XML PATH('') ), 1, 1, '') FROM Production.Product AS p2 GROUP BY p2.ProductID
接下来我们利用STUFF结合FOR XML PATH来拼接JSON字符串,如下:
DECLARE @content VARCHAR(MAX) SET @content = (SELECT '['+ STUFF((SELECT TOP 5 ',{"ProductName": "' + ProductName + '","Price": "' + CONVERT(VARCHAR, Price) + '","Quantity": "' + CONVERT(VARCHAR, quantity) + '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList FOR XML PATH('')), 1, 1,'' ) + ']'[ProductDetail]) PRINT @content
结果如上正确输出JSON字符串,接下来我们将如上拼接换行再试试。
DECLARE @content VARCHAR(MAX) SET @content = ( SELECT '[' + STUFF(( SELECT TOP 5 ',{"ProductName": "' + ProductName + '","Price": "' + CONVERT(VARCHAR, Price) + '","Quantity": "' + CONVERT(VARCHAR, quantity) + '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList FOR XML PATH('') ), 1, 1, '') + ']' [ProductDetail] ) PRINT @content
如上是利用SQL Prompt直接格式化换行,结果依然正确输出JSON字符串,我们再来手动换行试试。
DECLARE @content VARCHAR(MAX) SET @content = (SELECT '['+ STUFF((SELECT TOP 5 ', {"ProductName": "' + ProductName + '","Price": "' + CONVERT(VARCHAR, Price) + '","Quantity": "' + CONVERT(VARCHAR, quantity) + '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList FOR XML PATH('')), 1, 1,'' ) + ']'[ProductDetail]) PRINT @content
结果输出如上我们不期望的字符串,主要是由FOR XML PATH造成的,比如我们利用FOR XML PATH进行如下查询:
SELECT ' ' FOR XML PATH('')
当我们利用FOR XML PATH查询数据时,如果字符串中包含空格时会造成出现以如上错误的字符串来填充,所以此时我们为了消除这种错误格式,我们将上述继续添加参数。
SELECT ' ' FOR XML PATH(''),TYPE
此时我们将上述输出JSON字符串不错误的格式修改成如下即可:
DECLARE @content VARCHAR(MAX) SET @content = (SELECT '['+ STUFF((SELECT TOP 5 ', {"ProductName": "' + ProductName + '","Price": "' + CONVERT(VARCHAR, Price) + '","Quantity": "' + CONVERT(VARCHAR, quantity) + '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList FOR XML PATH('') ,TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,'' ) + ']'[ProductDetail]) PRINT @content
或者我们对上述输出的错误字符串进行替换,如下:
select t.PK, ltrim(rtrim(replace( (select ' ' + isnull(ti.Column1, '') + ' ' + isnull(ti.Column2, '') from yourTable ti where ti.PK = t.PK for xml path ('')) , ' ', ''))) fruits from yourTable t group by t.PK;
这里我们解决了利用STUFF有可能输出JSON字符串带有错误的字符串的问题,在利用STUFF输出JSON字符串时只要有一列数据包含NULL,那么返回的数据则为空,那么我们在对列数据通过ISNULL来进行判断,比如如下将输出NULL。
DECLARE @content VARCHAR(MAX) SET @content = (SELECT '['+ STUFF((SELECT TOP 5 ', {"ProductName": "' + NULL + '","Price": "' + CONVERT(VARCHAR, Price) + '","Quantity": "' + CONVERT(VARCHAR, quantity) + '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList FOR XML PATH('') ,TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,'' ) + ']'[ProductDetail]) PRINT @content
所以此时我们必须通过ISNULL来判断列数据是否为NULL,修改成如下形式:
DECLARE @content VARCHAR(MAX) SET @content = (SELECT '['+ STUFF((SELECT TOP 5 ', {"ProductName": "' + ISNULL(ProductName,'') + '","Price": "' + CONVERT(VARCHAR, Price) + '","Quantity": "' + CONVERT(VARCHAR, quantity) + '","Inserton": "' + CONVERT(VARCHAR, Inserton, 105) + '"}' FROM ProductList FOR XML PATH('') ,TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1,'' ) + ']'[ProductDetail]) PRINT @content
相关文章
- SQL Server-数据类型(七)
- sql server几种读写分离方案的比较
- Sql server在另一台服务器,在Visual Studio 中没问题,IIS中 提示“在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。。。。”
- 杂谈--SQL SERVER版本
- 解决开启SQL Server sql Always on Group 事务日志增大的问题
- SQL Server - Management Studio - Client Statistics - Wait time on server replies vs Client processing time
- 本人收藏的Sql server经典t-sql语句(备忘录,持续更新中)
- SQL Server 占用内存太高,查找占用内存高以及影响其性能的sql语句及解决方法
- SQL中用一句sql语句将数据库中的两列值进行交换
- SQL SERVER错误:已超过了锁请求超时时段。 (Microsoft SQL Server,错误: 1222) 解决方案
- SQL SERVER错误:已超过了锁请求超时时段。 (Microsoft SQL Server,错误: 1222)
- SQL Server下7种“数据分页”方案,全网最全
- 《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》——2.9 修改数据库属性
- 《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》导读
- SQL Server 2019 安装教程
- sql server 小技巧(8) visual studio 2013里使用Sql server compact 4.0及发布问题处理
- sql server 小技巧(4) Sql server 排序时让空值排在最后
- SQL Server: Get table primary key and Foreign Key using sql query
- SQL Server SQL性能优化之--通过拆分SQL提高执行效率,以及性能高低背后的原因
- SQL Server死锁排查
- SQL Server的分页优化及Row_Number()分页存在的问题
- 使用 SQL SERVER PROFILER 监测死锁
- sql server中同时执行select和update语句死锁问题
- 微软宣布Windows和Linux新版SQL Server预览
- sql server stolen内存问题,一次记录
- (4.61)sql server执行SQL遇到错误不中断
- SQL Server SSPI handshake failed报错,sql server修改windows账户密码的影响,sql server订阅发布客户端访问一直报错
- windows server 如要远程登录,你需要具有通过远程桌面服务进行登录的权限。
- (4.52)解决sql server动态SQL中对表字段操作时需要频繁 ISNULL的问题
- (1.2)sql server for linux 开启代理服务(SQL AGENT),使用T-SQL新建作业
- sql server存储引擎启动错误(SQL Server could not spawn FRunCM thread)
- 最小配置启动SQL SERVER,更改SQL Server最大内存大小导致不能启动的解决方法
- (4.21)sql server备份策略深入探究
- sql server 测试delete后数据空间情况
- SQL CHECK sql server免费监控单实例工具
- (4.7)怎么捕获和记录SQL Server中发生的死锁?
- SQL Server架构----SQL Server的执行模式和SQLOS
- [转]sql server 的ANSI_NULLS设置
- 卸载不干净,再次安装SQL Server报错:Could not open key:UNKNOWNComponents