利用sql拆分字符串

[SqlServer]数据库中自定义拆分字符串函数Split()

 

经常我们要用到批量操作时都会用到字符串的拆分,郁闷的是SQL Server中却没有自带Split函数,所以我们只能自己动手来解决一下。为了减少和数据库的通讯次数,我们都会利用这种方法来实现批量操作。当然有时我们会借助Execute这个方法来实现,利用这个方法有一个不好的地方就是她只认识以","分割的字符串,在传IDs批量操作的时候还是可以达到目的,但是经常我们要用到更复杂的操作时我们就需要自己动手来完成了......

1.当我们需要传入很长的字符串是我们可以借助NText和Text类型,他们的区别是一个是支持Unicode,一个是支持ANSI字符集的。需要注意的是当我们要计算字符串长度时我们需要用到DATALENGTH()而不是LEN(),在NText类型中一个字符占两个字节,所以在计算字符时别忘了除以2,下面我们先看下例子就能够说明一切了。
CREATE FUNCTION [dbo].[Split]
(
@SplitString text, -- 如果要传入NText类型,下面需要相应的修改,注释行为NText下同
@Separator varchar(2) = ','-- NVarChar(2) = N','
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] varchar(8000) -- NVarChar(4000)
)
AS
BEGIN
    DECLARE @CurrentIndex int;
    DECLARE @NextIndex int;
    DECLARE @ReturnText varchar(8000);-- NVarChar(4000)
    SELECT @CurrentIndex=1;
    WHILE(@CurrentIndex<=datalength(@SplitString)) -- DATALENGTH(@SplitString)/2
    BEGIN
        SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
        IF(@NextIndex=0 OR @NextIndex IS NULL)
            SELECT @NextIndex=datalength(@SplitString)+1;--DATALENGTH(@SplitString)/2
       
        SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);

        INSERT INTO @SplitStringsTable([value])
        VALUES(@ReturnText);
       
        SELECT @CurrentIndex=@NextIndex+1;
    END
    RETURN;
END



有时我们拆分出来还是需要很长的字符串有可能超过(N)VarChar的长度,当然为了兼容SQL Server2000不能用max,所以我们拆出的字符串还是要用(N)Text来表示,需要注意的是在局部变量中不能定义(N)Text的类型,不过我们可以把substring出来的字符串直接加入到表变量中,而不要付值后在Insert。

2.当我们传入的(N)VarChar时,我们可以用LEN来计算长度,值得注意的是NVarChar的最大长度是4000,而VarChar的最大长度是8000。下面我们来看一下代码,和上面的代码基本没什么差别。
CREATE FUNCTION [dbo].[Split]
(
@SplitString varchar(8000),-- nvarchar(4000)
@Separator varchar(2) = ','
)
RETURNS @SplitStringsTable TABLE
(
[id] int identity(1,1),
[value] varchar(8000)-- nvarchar(4000)
)
AS
BEGIN
    DECLARE @CurrentIndex int;
    DECLARE @NextIndex int;
    DECLARE @ReturnText varchar(8000);-- nvarchar(4000)
    SELECT @CurrentIndex=1;
    WHILE(@CurrentIndex<=len(@SplitString))
    BEGIN
        SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
        IF(@NextIndex=0 OR @NextIndex IS NULL)
            SELECT @NextIndex=len(@SplitString)+1;
       
        SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);

        INSERT INTO @SplitStringsTable([value])
        VALUES(@ReturnText);
       
        SELECT @CurrentIndex=@NextIndex+1;
    END
    RETURN;
END



3.拆分字符串,得到int类型的数据,这个比较简单,代码和上面的差不多这里就不给出了,可以根据上面的代码自己改写。

由于数据库中没有数组,所以只能用表变量返回,所以当你定义这些函数时要定义表值函数。OK有了这些函数我们就可以很好的利用他们来为我们的更有效的批量操作。当然由于变量都是考虑到溢出而设置的,肯能这样会给性能上带来一定的影响,但是编译后可能也可以给我们带来不少的效果,请大家慎用之。

 

实现SQL下的字符串拆分

 

因为工作的原因,开发过一个拆分字符串的SQL函数,现在把它贴出来,与大家共勉学习。

该函数如下:

CREATE function [dbo].[StringSplit](@str nvarchar(max),@spliter nvarchar(10))

returns @tb table(ch nvarchar(256))

AS

BEGIN

DECLARE @Num int,@Pos int, @NextPos int

SET @Num = 0

SET @Pos = 1

WHILE(@Pos <= LEN(@str))

BEGIN

SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)

IF (@NextPos = 0 OR @NextPos IS NULL)

SELECT @NextPos = LEN(@str) + 1

INSERT INTO @tb VALUES(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))))

SELECT @Pos = @NextPos+1

END

return

END

使用方法:

Syntax:

StringSplit (stringToSplit nvarchar(max) , separator nvarchar(10) )ArgumentsstringToSplit 用于分割的文本separator 分隔字符Samples在存储过程或者SQL脚本中,使用如下的方法

SELECT * FROM dbo.StringSplit(''计算机,IT,C#,SQL'','','')

得出的结果如下:

计算机

IT

C#

SQL

 

 

===========================

这样可以吗?  
  SQL>   create   table   tmp  
      2     (  
      3     name   varchar2(40),  
      4     ny   varchar2(10),  
      5     str   varchar2(100)  
      6     )  
      7     /  
   
  Table   created.  
   
  SQL>   insert   into   tmp   values   ('aa',   '200607',   '6,7');  
   
  1   row   created.  
   
  SQL>   insert   into   tmp   values   ('bb',   '200608',   '4,5,6,7,8,9');  
   
  1   row   created.  
   
  SQL>   select   *   from   tmp  
      2     /  
   
  NAME                                                                           NY                   STR  
  ----------------------------------------   ----------   ------------------------------------------------  
  aa                                                                               200607           6,7  
  bb                                                                               200608           4,5,6,7,8,9  
   
  SQL>   create   or   replace   procedure   substring_array(name   varchar2,   ny   varchar2,   str   in   varchar2)   as  
      2     type   array_str   is   table   of   varchar2(100)  
      3               index   by   binary_integer;  
      4     myarray   array_str;  
      5     v_str   varchar2(4000)   default   str;  
      6     begin  
      7         for   i   in   0   ..   length(str)-length(replace(str,',',''))+1   loop  
      8      
      9                 if   instr(v_str,',')   =   0   then    
    10                         myarray(i+1)   :=   v_str   ;  
    11                 else    
    12                         myarray(i+1)   :=   substr(v_str,0,instr(v_str,',')-1);                
    13                 end   if;  
    14                 v_str   :=   substr(v_str,instr(v_str,',')+1);  
    15         end   loop;  
    16          
    17         for   i   in   0   ..length(str)-length(replace(str,',',''))   loop  
    18                 dbms_output.put_line(name   ||   '   '   ||   ny   ||   '   '   ||   myarray(i+1));  
    19         end   loop   ;  
    20     end   substring_array;  
    21     /  
   
  Procedure   created.  
   
  SQL>   declare  
      2     name   varchar2(40);  
      3     ny   varchar2(10);  
      4     str   varchar2(100);  
      5     begin  
      6     for   c1   in   (select   name,ny,str   from   tmp)  
      7     loop  
      8     substring_array(c1.name,c1.ny,c1.str);  
      9     end   loop;  
    10     end;  
    11     /  
  aa   200607   6  
  aa   200607   7  
  bb   200608   4  
  bb   200608   5  
  bb   200608   6  
  bb   200608   7  
  bb   200608   8  
  bb   200608   9  
   
  PL/SQL   procedure   successfully   completed.   
    
  ===================================

一个自己写的拆分字符串的oraclefunction  


-- 按照给定的字节长度截取最大可能的中英文混合字符串,避免了半个汉字的问题
create or replace function substr_gb(str_    varchar2,
                                     begin_  integer,
                                     length_ integer) return varchar2 is
    result varchar2(256);
begin
    if length(substrb(str_, begin_, length_)) =
       length(substrb(str_, begin_, length_ + 1)) then
        result := substrb(str_, begin_, length_ - 1);
    else
        result := substrb(str_, begin_, length_);
    end if;
    return result;
end substr_gb;
<think>好的,用户需要处理一个Access VBA的问题,他们有一个表,其中LOC字段是用分号分隔的字符串,想要写一个函数来拆分这些字符串成多行,类似SQL Server的STRING_SPLIT功能。首先,我需要理解用户的具体需求。他们可能是在数据导入或处理时遇到了需要将一列中的多个值拆分成独立记录的情况。例如,某个记录的LOC字段是“A;B;C”,需要拆分成三个记录,每个记录分别包含A、B、C。 接下来,我得考虑Access的环境限制。Access的SQL不支持内置的拆分函数,所以必须用VBA来实现。用户可能已经尝试过其他方法,但发现效率不高或者不能满足需求。需要提供一个可靠且高效的解决方案。 首先,拆分字符串的VBA函数。通常,拆分字符串可以用Split函数,将字符串按分号分割成数组,然后遍历数组生成结果。但用户需要的是将原表中的每一行拆分成多行,所以需要处理整个表,而不仅仅是单个字符串。 然后,考虑如何处理原表的数据。可能需要遍历原表的每一条记录,对每个LOC字段进行拆分,然后将每个拆分后的值插入到新表中。需要注意保留原表的其他字段,比如用户提到的ID和LOC,拆分后的每个记录应该保留ID和对应的拆分后的LOC值。 接下来,性能问题。如果原表数据量大,直接逐条处理可能会很慢。所以需要考虑使用事务来批量提交,或者优化循环结构。例如,使用事务来减少每次插入的开销,或者将数据暂存在数组中,最后一次性写入。 另外,用户提到要类似SQL Server的STRING_SPLIT函数,可能需要返回一个表形式的结果。在Access中,可以创建一个函数返回记录集,或者直接生成一个新表。根据用户的需求,可能更倾向于生成一个新表,或者更新现有表的结构。 然后,处理可能存在的分号周围空格问题。比如“A; B; C”中的空格是否需要去除。在代码中,可能需要使用Trim函数来清理每个拆分后的值,避免多余的空格影响数据一致性。 还要考虑错误处理,比如原表中的某些记录的LOC字段可能为空,或者分号分隔符不正确。代码中需要处理这些边缘情况,避免运行时错误。 最后,代码的可重用性。用户可能需要多次执行这个操作,或者将其作为日常处理的一部分,所以函数需要封装良好,方便调用,并且参数化表名和字段名,提高灵活性。 综上所述,解决方案需要包括以下几个步骤: 1. 创建一个拆分字符串的函数,返回数组。 2. 遍历原表的每条记录,拆分LOC字段。 3. 将拆分后的每个值插入到目标表中,保留原记录的其他字段。 4. 使用事务处理提高性能。 5. 处理可能的错误和边缘情况。 6. 提供示例代码和调用方法,方便用户直接使用。 需要验证代码的正确性,例如测试不同情况下的拆分结果,确保分号分隔的不同情况都能正确处理。同时,考虑到Access的环境,可能需要使用DAO或ADODB来处理数据访问,这里选择DAO可能更符合Access的常规做法。 最后,整理代码并提供注释,确保用户能够理解每一步的作用,同时给出使用示例,说明如何调用该函数,并提醒注意事项,比如备份数据和性能考虑。</think>在 Access VBA 中实现类似 SQL Server `STRING_SPLIT` 的功能,可以通过以下完整解决方案实现: --- ### 一、核心拆分函数 ```vba ' 函数:拆分字符串为记录集 (类似 STRING_SPLIT) ' 输入:strInput - 原始字符串, strDelimiter - 分隔符 ' 输出:拆分后的二维数组(保留原始数据关联性) Public Function SplitToRecords(ByVal strInput As String, Optional strDelimiter As String = ";") As Variant If Len(strInput) = 0 Then Exit Function Dim arrItems() As String arrItems = Split(strInput, strDelimiter) ' 清理空格并构建二维数组 Dim arrResult() As Variant ReDim arrResult(UBound(arrItems), 1) Dim i As Long For i = 0 To UBound(arrItems) arrResult(i, 0) = i + 1 ' 序号列 arrResult(i, 1) = Trim(arrItems(i)) ' 去空格 Next SplitToRecords = arrResult End Function ``` --- ### 二、数据迁移完整代码 ```vba Sub SplitLocField() On Error GoTo ErrorHandler Dim db As DAO.Database Dim rsSource As DAO.Recordset Dim rsTarget As DAO.Recordset Set db = CurrentDb() ' 创建目标表(仅首次运行时需要) If Not TableExists("拆分后表") Then db.Execute "CREATE TABLE 拆分后表 (" & _ "原始ID LONG, " & _ "拆分序号 LONG, " & _ "拆分值 TEXT(255))" End If ' 事务处理提升性能 db.BeginTrans ' 清空目标表(可选) 'db.Execute "DELETE FROM 拆分后表" ' 打开源表和目标表 Set rsSource = db.OpenRecordset("SELECT ID, LOC FROM 原始表 WHERE LOC IS NOT NULL") Set rsTarget = db.OpenRecordset("拆分后表") Do While Not rsSource.EOF Dim arrSplit As Variant arrSplit = SplitToRecords(rsSource!LOC.Value) If Not IsEmpty(arrSplit) Then Dim i As Long For i = LBound(arrSplit) To UBound(arrSplit) rsTarget.AddNew rsTarget!原始ID = rsSource!ID rsTarget!拆分序号 = arrSplit(i, 0) rsTarget!拆分值 = arrSplit(i, 1) rsTarget.Update Next End If rsSource.MoveNext Loop db.CommitTrans CleanExit: rsSource.Close rsTarget.Close Set rsSource = Nothing Set rsTarget = Nothing Exit Sub ErrorHandler: db.Rollback MsgBox "错误号:" & Err.Number & vbCrLf & "描述:" & Err.Description, vbCritical Resume CleanExit End Sub ' 辅助函数:检查表是否存在 Private Function TableExists(strTableName As String) As Boolean TableExists = DCount("*", "MSysObjects", "Name='" & strTableName & "' AND Type=1") > 0 End Function ``` --- ### 三、使用示例 #### 原始表数据 | ID | LOC | |----|--------------| | 1 | 北京;上海 | | 2 | 广州;深圳;珠海 | #### 运行后目标表结果 | 原始ID | 拆分序号 | 拆分值 | |--------|----------|--------| | 1 | 1 | 北京 | | 1 | 2 | 上海 | | 2 | 1 | 广州 | | 2 | 2 | 深圳 | | 2 | 3 | 珠海 | --- ### 四、高级功能扩展 #### 1. 保留其他字段 修改代码中的目标表结构,添加需要保留的字段: ```vba ' 创建表示例 db.Execute "CREATE TABLE 拆分后表 (" & _ "原始ID LONG, " & _ "拆分序号 LONG, " & _ "拆分值 TEXT(255), " & _ "原记录创建日期 DATETIME)" ' 新增字段 ``` 在循环中添加数据: ```vba rsTarget!原记录创建日期 = rsSource!CreateDate ' 假设源表有此字段 ``` #### 2. 性能优化建议 - **索引优化**:为目标表的`原始ID`字段添加索引 - **批量提交**:每处理500条后提交事务 ```vba Dim lngCounter As Long lngCounter = 0 Do While Not rsSource.EOF ' ...处理代码... lngCounter = lngCounter + 1 If lngCounter Mod 500 = 0 Then db.CommitTrans db.BeginTrans End If Loop ``` --- ### 五、注意事项 1. **分号嵌套问题**:不支持类似`"值1;值2;内有;分号的值"`的复杂情况 2. **空值处理**:自动跳过`LOC`字段为空的记录 3. **最大长度**:单字段最多拆分约16,000个字符(Access限制) 4. **去重需求**:如需去重,可在插入前添加判断: ```vba If DCount("*", "拆分后表", "拆分值='" & Replace(arrSplit(i, 1), "'", "''") & "'") = 0 Then ' 执行插入 End If ``` 这个方案完整实现了字段拆分、数据关联、事务控制等关键功能,可直接复制到 VBA 模块中使用。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值