利用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;