求一类似split的函数(返回nvarchar)如dbo.split('aa,bb',1)返回aa

Question

求一函数,实现类似 split 功能如@a = 'aa$$b呵呵$$天下无敌$$真二$$很黄$$很暴力'
select dbo.split(@a,2)返回
'b呵呵'

Answer 1

[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;
END3.拆分字符串，得到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; 

Answer 2

create table ta( col varchar(100))
insert ta select
'aa/bb/cc/dd/ee/ff' union all select
'aaa/bbb/ccc/ddd/eee/ff/ggg'go--加一个分隔函数:
create  function F_split(
                @s varchar(8000),          --包含多个数据项的字符串
                @pos int,                 --要获取的数据项的位置
                @split varchar(10)        --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
    IF @s IS NULL RETURN(NULL)
    DECLARE @splitlen int                --分隔符长度
    SELECT @splitlen=LEN(@split+'a')-2
    WHILE @pos>1 AND charindex(@split,@s+@split)>0
        SELECT @pos=@pos-1,
            @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
    RETURN(nullif(left(@s,charindex(@split,@s+@split)-1),''))
END
GOselect dbo.F_split(col,1,'/'),dbo.F_split(col,3,'/'),dbo.F_split(col,5,'/')
from tadrop table ta
drop function F_split15951087864 ma

Answer 3

楼主对split的要求没有说清楚吧。不过貌似直接使用substring就行了

Answer 4

CREATE FUNCTION F_SPLIT(@STR NVARCHAR(50), @POS INT)
RETURNS NVARCHAR(50)
AS
BEGIN
  DECLARE @P INT
  SET @P=0
  WHILE CHARINDEX(N'$$', @STR+N'$$')>0
  BEGIN
    SET @P=@P+1
    IF @P=@POS
    BEGIN
      SET @STR=LEFT(@STR, CHARINDEX(N'$$', @STR+N'$$')-1) 
      BREAK
    END
    SET @STR=STUFF(@STR, 1, CHARINDEX(N'$$', @STR+N'$$')+1, N'')     
  END
  RETURN @STR
END
GODECLARE  @A NVARCHAR(50)
SET @a = N'aa$$b呵呵$$天下无敌$$真二$$很黄$$很暴力'SELECT dbo.F_SPLIT(@a, 2)DROP FUNCTION F_SPLIT
/*
b呵呵
*/

Answer 5

如果是sql2005，可以考虑用clr，嘿嘿http://topic.csdn.net/u/20090423/00/bf68f001-c5bb-486b-b473-88101f05727d.html只要用类似c# 的 split(c)[n]返回一个SqlString就可以了。

Answer 6

--按指定符号分割字符串，返回分割后指定索引的第几个元素，象数组一样方便    CREATE function split
    (
    @str varchar(1024), --要分割的字符串
    @split varchar(10), --分隔符号
    @index int --取第几个元素
    )
    returns varchar(1024)
    as
    begin
   declare @location int
   declare @start int
   declare @next int
   declare @seed int
  
   set @str=ltrim(rtrim(@str))
   set @start=1
   set @next=1
   set @seed=len(@split)
  
   set @location=charindex(@split,@str)
   while @location<>0 and @index>@next
   begin
   set @start=@location+@seed
   set @location=charindex(@split,@str,@start)
   set @next=@next+1
   end
   if @location =0 select @location =len(@str)+1
   return substring(@str,@start,@location-@start)
   end

Answer 7

非常感谢1,2楼,已搞定..1楼的函数返回table,二楼的合我意..结贴..

Answer 8

if object_id('tb') is not null drop table tb
gocreate table tb (a varchar(50))
insert tb select 'aa$$b呵呵$$天下无敌$$真二$$很黄$$很暴力'goif object_id('getstr') is not null drop function getstr
gocreate function getstr(@s varchar(50))
returns varchar(50)
as
begin
  return (left(substring(@s,charindex('$$',@s)+2,len(@s)-charindex('$$',@s)-1),charindex('$$',substring(@s,charindex('$$',@s)+2,len(@s)-charindex('$$',@s)-1))-1))
end
goselect dbo.getstr(a) from tb
/*
--------------------------------------------------
b呵呵(1 行受影响)
*/

Answer 9

字符串拆分，一般就是charindex,substring,left等等函数的使用