SQLServer字符串批量拆分得方法

最新推荐文章于 2024-08-17 22:30:13 发布
最新推荐文章于 2024-08-17 22:30:13 发布 · 139 阅读 · 1
文章标签：

#数据库

本文详细介绍了SQLServer中用于字符串拆分的函数及其实现过程，通过示例展示了如何使用该函数对多值字段进行高效处理。

其实拆分和批量拆分的方法是一样的，所谓批量就是用out apply来调用单个拆分函数。

/*
SQLServer字符串拆分函数,by jinjazz

--原始数据
id names
----------- --------------------
1 jinjazz,blog,csdn
2 sql,ms

--生成的数据
id rn name
----------- ----------- ----------
1 1 jinjazz
1 2 blog
1 3 csdn
2 1 sql
2 2 ms


*/

setnocounton
usetempdb
go
if (object_id ('f_test' ) isnotnull )
dropfunctionf_test
go
createfunctionf_test (@avarchar (max ))
returns @ttable (rnint , vvarchar (max ))
as
begin
insertinto @t
select b. *from (
selectconvert (xml , '<v>' + replace (@a , ',' , '</v><v>' )+ '</v>' ) asf )a
outerapply
(
SELECTrn = row_number ()over (orderbygetdate ()),t . c . value ('.' , 'varchar(max)' ) ASf
FROMa . f . nodes ('//v' ) ASt (c )
)b
return
end
go


declare @ttable (idint , namesvarchar (20 ))
insertinto @tselect1 , 'jinjazz,blog,csdn'
insertinto @tselect2 , 'sql,ms'

select*from @t
selecta . id , rn , b. vasnamefrom @taouterapplydbo . f_test (a . names ) b

setnocountoff


本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/jinjazz/archive/2009/08/27/4490912.aspx

其实拆分和批量拆分的方法是一样的，所谓批量就是用out apply来调用单个拆分函数。

/*
SQLServer字符串拆分函数,by jinjazz

--原始数据
id names
----------- --------------------
1 jinjazz,blog,csdn
2 sql,ms

--生成的数据
id rn name
----------- ----------- ----------
1 1 jinjazz
1 2 blog
1 3 csdn
2 1 sql
2 2 ms


*/

setnocounton
usetempdb
go
if (object_id ('f_test' ) isnotnull )
dropfunctionf_test
go
createfunctionf_test (@avarchar (max ))
returns @ttable (rnint , vvarchar (max ))
as
begin
insertinto @t
select b. *from (
selectconvert (xml , '<v>' + replace (@a , ',' , '</v><v>' )+ '</v>' ) asf )a
outerapply
(
SELECTrn = row_number ()over (orderbygetdate ()),t . c . value ('.' , 'varchar(max)' ) ASf
FROMa . f . nodes ('//v' ) ASt (c )
)b
return
end
go


declare @ttable (idint , namesvarchar (20 ))
insertinto @tselect1 , 'jinjazz,blog,csdn'
insertinto @tselect2 , 'sql,ms'

select*from @t
selecta . id , rn , b. vasnamefrom @taouterapplydbo . f_test (a . names ) b

setnocountoff


本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/jinjazz/archive/2009/08/27/4490912.aspx

iteye_20105
关注
SQLSERVER 字符串分割
半雨微凉
07-31 2万+
情景描述： 项目中有个地方需要向数据库中插入几条数据，由此引发的一系列问题~ 项目要求： 1、使用存储过程 2、代码和数据库相关业务分离 以下为几种方案： 1、最初的方案是存储过程中插入一条数据，代码中循环调用存储过程进行插入（被PASS，原因是多次调用影响效率） 2、代码中拼接成一条SQL语句，然后传给存储过程，执行一次操作即可（被PASS，要实现业务分离） 3、通过文件批量插入...
SQL Server字符串切割函数
520_LH
01-12 855
SQL Server中把一个字符串切割成多个数据函数 CREATE FUNCTION SplitStringToTable ( in Input VARCHAR(MAX(VARCHAR)) , -- 输入切割的字符串 in Separator VARCHAR(MAX(VARCHAR)) = ',' -- 切割标识 ) RETURNS out TABLE TABLE ( Id INT IDENTITY(1, 1) , -- 返回一个表有i
sqlserver中根据某个字符切割字符串函数
08-29
sqlserver中根据某个字符切割字符串函数，比如根据逗号切割字符串，结果返回的是一个表值函数，这个函数返回结果字段包含Id和Value，Id为序号，Value为切割后的值
sqlserver字符串拆分函数
weixin_34321753的博客
12-28 237
CREATE FUNCTION f_splitSTR(@s varchar(8000), --待分拆的字符串@split varchar(10) --数据分隔符)RETURNS @re TABLE(col varchar(100))ASBEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2 WHILE...
SQLserver字符串分割函数
weixin_30667301的博客
06-06 419
一、按指定符号分割字符串，返回分割后的元素个数，方法很简单，就是看字符串中存在多少个分隔符号，然后再加一，就是要求的结果。CREATEfunctionGet_StrArrayLength(@strvarchar(1024),--要分割的字符串@splitvarchar(10)--分隔符号)returnsintasbegindeclare@locationint...
SQL Server实现将特定字符串拆分并进行插入操作的方法
09-10
在SQL Server中，有时我们需要处理包含多个值的单一字符串，并将其拆分成单独的记录，然后将这些值插入到数据库表中。这通常涉及到字符串处理、循环以及条件判断等操作。以下是一个具体的示例，演示了如何实现这个...
SQLServer中的切割字符串SplitString函数
09-11
在SQL Server中，当需要将一个字符串按照特定的分隔符进行拆分成多个子字符串时，通常会遇到问题，因为SQL Server的标准库中并没有提供内置的Split函数。为了解决这个问题，开发人员经常需要创建自定义的函数来实现...
SQLServer逗号分隔的字符串转换成表
06-17
2. 利用 SQL Server 函数进行字符串拆分：可以使用 SQL Server 的字符串函数，例如 CHARINDEX、LEFT、RIGHT、LEN 等函数对字符串进行拆分。 3. 将拆分后的数据写入到临时表中：将拆分后的数据写入到第 1 步中创建的...
Sql Server数据库中自定义拆分字符串函数Split()
05-07
Sql Server数据库中自定义拆分字符串函数Split()
sqlserver2008 拆分字符串
12-15
–说明:例如,将下列数据 id id_value —————– 1 ‘aa,bb’ 2 ‘aa,bb,cc’ 3 ‘aaa,bbb,ccc’ –转换成以下的格式 id id_value —————– 1 ‘aa’ 1 ‘bb’ 2 ‘aa’ 2 ‘bb’ 2 ‘cc’ 3 ‘aaa’ 3 ‘bbb’ 3 ‘ccc’ –代码——————————————-GO 代码如下:create table ta ( id int, id_value varchar(30) ) insert into ta select 1,’aa,bb’ union all select 2,’aa,bb,cc’ uni
全国省市县三级数据库，内含sql server(三个分表2008r2)和access(一个总表)两个数据库和access数据库导入sql sever的方法以及整表分成三个分表的sql执行代码
12-18
全国省市县三级数据库 内含sql server(三个分表2008r2)和access(一个总表)两个数据库和access数据库导入sql sever的方法以及整表分成三个分表的sql执行代码
全国省市县数据库sql server
04-16
从一个mysql数据库导过来的，算是挺新的数据库吧。 把sql语句里第一句的USE [HotelManager] GO中HotelManager改成你建立的数据库名。。。 T_province表 省 自治区 直辖市(一级) [serialId] [bigint] NOT NULL Id [provinceId] [nvarchar] NULL 省id [provinceName] [nvarchar] NULL 省名 [provinceUpId] [nvarchar] NULL 上一级id [provinceUpIdNum] [int] NULL 上一级名字 [provincePath] [nvarchar] NULL 省路径 [provinceType] [nvarchar] NULL 类型 [provinceTypeNum] [int] NULL 类型等级 [shortName] [nvarchar] NULL 简称(汉语拼音) [spell] [nvarchar] NULL 拼音拼写 [areaId] [nvarchar] NULL 区域id [postCode] [nvarchar] NULL 邮政编码 T_city表 市 县 区 地区 州（二级） serialId bigint NOT NULL Id cityId nvarchar NULL 市id cityName nvarchar NULL 市名 cityUpId nvarchar NULL 上一级id cityUpIdNum nvarchar NULL 上一级名字 cityPath nvarchar NULL 完整路径 cityType nvarchar NULL 市类型 cityTypeNum bigint NULL 类型等级 shortName nvarchar NULL 简称（汉语拼音） spell nvarchar NULL 拼音拼写 areaId nvarchar NULL 区域id postCode nvarchar NULL 邮政编码 T_district 区域 （三级） serialId bigint NOT NULL Id districtId nvarchar NULL 区域id districtName nvarchar NULL 区域名 districtUpId nvarchar NULL 上一级id districtUpIdNum int NULL 上一级名字 districtPath nvarchar NULL 完整路径 districtType nvarchar NULL 类型 districtTypeNum int NULL 类型等级 shortName nvarchar NULL 简称（汉语拼音） spell nvarchar NULL 拼音拼写 areaId nvarchar NULL 区域id postCode nvarchar NULL 邮政编码
[SqlServer]数据库中自定义拆分字符串函数Split() (转)
weixin_30765577的博客
10-11 205
[SqlServer]数据库中自定义拆分字符串函数Split() 经常我们要用到批量操作时都会用到字符串的拆分，郁闷的是SQL Server中却没有自带Split函数，所以我们只能自己动手来解决一下。为了减少和数据库的通讯次数，我们都会利用这种方法来实现批量操作。当然有时我们会借助Execute这个方法来实现，利用这个方法有一个不好的地方就是她只认识以","分割的字符串，在传IDs批量操作...
sql server 把字符串拆分
moshengrenhere的博客
10-04 1667
SELECT *, SUBSTRING(a.n, iter.pos, 1) AS c FROM (SELECT 'asdf1*h456' AS n) a, tb_num06 asiter WHERE iter.startN<= LEN(a.n) SUBSTRING 要进行截取的字符串，从第几个开始...
sqlserver拆分字符串函数 及应用
Mr_Wanter
05-09 3780
USE [dataChange] GO /****** Object: UserDefinedFunction [dbo].[f_splitSTR] Script Date: 05/09/2016 15:04:21 自己修改数据库名，直接执行就可以了******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION
sqlserver 分割字符串
上善若水
07-23 323
[code="sql"] USE [WANSYSTEMDB] GO /****** Object: UserDefinedFunction [dbo].[splitSTR] Script Date: 07/23/2012 23:23:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCT...
[sqlserver][sql]将一个字符串按照分隔符进行分割
一名不太专业的程序员
08-17 3640
这个函数可以在 SQL Server 中使用，用于根据指定的分隔符将字符串分割成多个部分。进行分割，并将分割后的子字符串以表的形式返回。在循环内部，它将分隔符前的子字符串插入到表。中已经被处理的部分（从开始到分隔符）。中剩余的部分（不包含分隔符）插入到表。的列，用于存储分割后的子字符串。中不再包含分隔符时，循环结束。是用于分割字符串的分隔符。是需要被分割的字符串，的表，表中有一个名为。循环来不断查找字符串。循环结束后，将字符串。
C#中是否有SQL server的的批量更新方法
最新发布
06-13
<think>嗯，用户这次想了解C#中对SQLServer进行批量更新的方法。从查询内容看，用户应该是个中级开发者，正在优化数据库操作性能，尤其是面对大量数据更新的场景。用户之前可能用过逐条更新或者简单拼接SQL的方式，现在遇到了效率瓶颈。用户提供的三个引用很有意思：[1]提到用DataTable参数传递实现跨数据库更新，[2]展示传统SQL拼接更新，[3]则是SqlBulkCopy用于批量插入。但用户明确要的是批量更新方案，所以需要重点提取与更新相关的技术点。考虑到实际开发场景，应该给用户分层级的解决方案：基础版（字符串拼接）、进阶版（TVP表值参数）、高性能版（SqlBulkCopy+临时表）。特别要注意的是，用户引用[1]里提到用存储过程处理DataTable的方案，实际测试70万数据只需4分钟，这个实战数据很有说服力，必须包含在方案里。用户可能没意识到的是，TVP方案需要SQLServer2008+版本支持，这点要重点说明。另外引用[3]的SqlBulkCopy虽然主要用途是插入，但配合临时表就能转向批量更新，这个巧妙用法值得展开。需要提醒事务处理和数据验证的风险点，特别是企业级应用中必须考虑这些。用户提到的“地址拆分”案例也说明实际业务可能伴随额外处理逻辑，方案要保留扩展性。最后生成的问题要覆盖后续可能的需求：用户接下来可能会问参数化细节、版本兼容问题，或者想比较不同方案的精确性能数据，这些都要预判到。</think> 好的！在 C# 中高效地对 SQL Server 进行批量更新，常见且推荐的方法主要有以下几种： **1. 表值参数 (Table-Valued Parameters, TVP)** **描述**：这是 SQL Server 2008 及更高版本推荐的方式。它允许你将内存中的数据结构（如 `DataTable` 或自定义表类型）作为参数传递给存储过程。 **优点**：类型安全、高效（在数据库内进行操作）、天然支持批处理、参数化避免 SQL 注入。 **步骤**： * **在 SQL Server 中定义自定义表类型**：定义与要更新的数据行结构匹配的表类型。 ```sql CREATE TYPE dbo.BulkUpdateDataType AS TABLE ( ID INT PRIMARY KEY, NewColumn1 NVARCHAR(50), NewColumn2 INT, ... -- 其他要更新的列 ); ``` * **在存储过程中使用该表类型参数**：编写处理批量更新的存储过程。 ```sql CREATE PROCEDURE dbo.usp_BulkUpdateFromTVP @Updates dbo.BulkUpdateDataType READONLY AS BEGIN UPDATE t SET t.Column1 = u.NewColumn1, t.Column2 = u.NewColumn2, ... FROM YourTargetTable t INNER JOIN @Updates u ON t.ID = u.ID; -- 根据主键ID关联 END ``` * **在 C# 端准备数据并调用**： ```csharp // 1. 创建一个与自定义表类型结构匹配的 DataTable DataTable updateDataTable = new DataTable(); updateDataTable.Columns.Add("ID", typeof(int)); updateDataTable.Columns.Add("NewColumn1", typeof(string)); updateDataTable.Columns.Add("NewColumn2", typeof(int)); // ... 添加其他列 // 2. 填充要更新的数据行 foreach (var item in yourUpdateItems) { updateDataTable.Rows.Add(item.ID, item.NewValue1, item.NewValue2, ...); } // 3. 准备 SqlCommand 并添加 TVP 参数 using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("usp_BulkUpdateFromTVP", connection)) { command.CommandType = CommandType.StoredProcedure; // 添加 TVP 参数，类型名需匹配 SQL 中的自定义类型名 SqlParameter param = command.Parameters.AddWithValue("@Updates", updateDataTable); param.SqlDbType = SqlDbType.Structured; param.TypeName = "dbo.BulkUpdateDataType"; // 指定类型名（含schema） // 4. 执行更新 command.ExecuteNonQuery(); } } ``` **2. 使用 `SqlBulkCopy` + 临时表/表变量 + 存储过程** **描述**：`SqlBulkCopy` 以极高的速度将大量数据从 C# 加载到 SQL Server 中的临时表或表变量中。然后，再通过调用存储过程，在这个临时表和目标表之间进行关联更新（使用 `UPDATE ... FROM ... JOIN` 语法）。 **优点**：性能卓越，尤其适用于海量数据更新。`SqlBulkCopy` 是专门为高速数据传输设计的。 **步骤**： * **在 SQL Server 中创建存储过程**：该过程负责接收临时表名（或使用内部表变量）并执行实际的 `UPDATE` 操作。 ```sql CREATE PROCEDURE dbo.usp_BulkUpdateViaBulkCopy @StagingTableName NVARCHAR(128) -- 或者直接传递数据到表变量 AS BEGIN -- 如果使用表变量，则在过程内部定义并使用 @StagingData -- DECLARE @StagingData dbo.BulkUpdateDataType (假设已定义) -- INSERT INTO @StagingData EXEC('SELECT * FROM ' + @StagingTableName) -- 若使用真实临时表 UPDATE t SET t.Column1 = s.NewColumn1, t.Column2 = s.NewColumn2, ... FROM YourTargetTable t INNER JOIN @StagingData s ON t.ID = s.ID; -- 关联临时结构更新目标表 -- 若使用真实临时表: INNER JOIN tempdb..##YourTempTable s ON ... (注意全局临时表作用域) END ``` * **在 C# 端使用 `SqlBulkCopy`**： ```csharp using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // a. 创建一个唯一的临时表名 (例如会话ID+随机数，或用全局临时表) string tempTableName = "#TempBulkUpdate_" + Guid.NewGuid().ToString("N"); // b. 在数据库中创建临时表结构 (结构应与你的更新DataTable一致) using (SqlCommand createCmd = new SqlCommand( $"CREATE TABLE {tempTableName} (ID INT PRIMARY KEY, NewColumn1 NVARCHAR(50), NewColumn2 INT, ...)", connection)) { createCmd.ExecuteNonQuery(); } // c. 使用 SqlBulkCopy 将数据 (DataTable) 快速写入临时表 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = tempTableName; // 写入临时表 // 如果需要，配置 ColumnMappings (如果你的DataTable列名和临时表列名不完全一致) // bulkCopy.ColumnMappings.Add("DataTableCol1", "TempTableCol1"); // ... DataTable updateDataTable = ... // 构造更新数据 DataTable (同方法1) bulkCopy.WriteToServer(updateDataTable); } // d. 调用存储过程，传递临时表名，让它完成 UPDATE JOIN 操作 using (SqlCommand updateCmd = new SqlCommand("usp_BulkUpdateViaBulkCopy", connection)) { updateCmd.CommandType = CommandType.StoredProcedure; updateCmd.Parameters.AddWithValue("@StagingTableName", tempTableName); updateCmd.ExecuteNonQuery(); } // e. (可选) 删除临时表 using (SqlCommand dropCmd = new SqlCommand($"DROP TABLE {tempTableName}", connection)) { dropCmd.ExecuteNonQuery(); } } ``` **3. 使用 `MERGE` 语句与 TVP 或 `SqlBulkCopy` 结合** **描述**：SQL Server 的 `MERGE` 语句是专门为“存在则更新/插入/删除，不存在则插入/删除”操作设计的。将你的批量数据（通过 TVP 或 `SqlBulkCopy` 写入到临时结构）传递给一个包含 `MERGE` 语句的存储过程。 **优点**：`MERGE` 在一个原子操作中处理所有逻辑（匹配的更新，不匹配的插入等），非常强大且通常高效。结合 TVP 或 `SqlBulkCopy` 加载数据。 **步骤**： * **在 SQL Server 中创建使用 `MERGE` 的存储过程**：接收数据源（TVP 或临时表）。 ```sql CREATE PROCEDURE dbo.usp_MergeUpdatesFromTVP @Updates dbo.BulkUpdateDataType READONLY -- 假设使用TVP AS BEGIN MERGE INTO YourTargetTable AS tgt USING @Updates AS src ON (tgt.ID = src.ID) WHEN MATCHED THEN UPDATE SET tgt.Column1 = src.NewColumn1, tgt.Column2 = src.NewColumn2, ... -- 可选： WHEN NOT MATCHED BY TARGET THEN INSERT ... (如果也想包含插入) -- 可选： WHEN NOT MATCHED BY SOURCE THEN DELETE ... (如果也想基于源删除目标) ; END ``` * **在 C# 端提供批量数据**：使用 TVP （如方法1）或 `SqlBulkCopy` + 临时表（如方法2，然后传递临时表名给存储过程调用 `MERGE`）。 **4. (不推荐用于大批量或复杂业务) 参数化 SQL 语句拼接 (动态生成带有 `WHERE` 条件的 `UPDATE`)** **描述**：小心地构建一个包含多个 `UPDATE` 操作的 SQL 脚本（每行更新一个记录）。这通常涉及到循环你的集合并拼接参数化的 `UPDATE` 语句。必须使用参数化查询！ **适用场景**：数据量非常小（几十条），或者上述方案由于某些原因不可行。 **缺点**： * **效率较低**：每条 `UPDATE` 独立执行，涉及多次网络往返和 SQL 编译/执行开销。 * **潜在性能问题**：大数量时执行时间长，易阻塞。 * **复杂性增加/易出错**：需要动态生成 SQL 字符串，需格外注意 SQL 注入防护。 * **事务控制**：通常需要显式事务。 **示例**： ```csharp using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlTransaction transaction = connection.BeginTransaction(); // 建议事务 try { StringBuilder sb = new StringBuilder(); List paramsList = new List(); int paramIndex = 0; foreach (var item in updateItems) { sb.AppendLine($"UPDATE YourTable SET Column1 = @p{paramIndex}_val1, Column2 = @p{paramIndex}_val2 WHERE ID = @p{paramIndex}_id;"); paramsList.Add(new SqlParameter($"@p{paramIndex}_id", item.ID)); paramsList.Add(new SqlParameter($"@p{paramIndex}_val1", item.NewVal1)); paramsList.Add(new SqlParameter($"@p{paramIndex}_val2", item.NewVal2)); paramIndex++; } using (SqlCommand command = new SqlCommand(sb.ToString(), connection, transaction)) { command.Parameters.AddRange(paramsList.ToArray()); int rowsAffected = command.ExecuteNonQuery(); // 可能需要检查 rowsAffected 是否符合预期 } transaction.Commit(); } catch { transaction.Rollback(); throw; } } ``` **总结与建议** * **效率优先 (SQL 2008+)**: **方法 1 - TVP 存储过程**通常是最直接、平衡且推荐的方案。它在数据库引擎内完成所有工作，效率高、安全。 * **海量数据性能极致**: 如果更新数据量极大（数十万/百万行），**方法 2 - `SqlBulkCopy` + 临时表 + 存储过程 UPDATE JOIN** 通常是性能最优的方案。 * **包含插入/删除逻辑**: **方法 3 - `MERGE` 语句 + TVP 或 `SqlBulkCopy`** 最为强大和简洁。 * **小批量更新或特殊约束**: **方法 4 - 参数化SQL拼接**可作为后备方案，需注意效率。 **重要注意事项** * **事务**：所有方法都强烈建议在事务中执行，以保持数据一致性。 * **错误处理**：务必进行完善的错误处理（`try-catch-finally`），特别是对于资源和事务的管理。 * **验证与权限**：应用程序端应对输入数据进行验证，确保安全且有效。数据库账号需有执行存储过程、创建临时表（如果需要）、访问相关对象等必要权限。 * **性能测试**：选择哪种方法最终取决于具体场景和数据量。务必在实际环境和数据规模下进行性能测试，选择最适合的方案。 实际项目中，方案三更新一万条数据，花费时间不到一秒钟。结合地址拆分的复杂运算，70万条数据更新完毕耗时也仅4分钟左右（主要是拆分的计算开销）[^1]。这清晰展现了 `SqlBulkCopy` 加临时表策略在处理巨量数据更新时的卓越性能。 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值