sql查询里 怎么拆分字符串(按“/”拆分)

发布网友 发布时间：2022-04-28 22:32

我来回答

共4个回答

懂视网 时间：2022-04-07 22:31

Create FUNCTION [dbo].[F_Split]
 (
 @SplitString nvarchar(max), --源字符串
 @Separator nvarchar(10)=‘ ‘ --分隔符号，默认为空格
 )
 RETURNS @SplitStringsTable TABLE --输出的数据表
 (
 [id] int identity(1,1),
 [value] nvarchar(max)
 )
 AS
 BEGIN
 DECLARE @CurrentIndex int;
 DECLARE @NextIndex int;
 DECLARE @ReturnText nvarchar(max);

 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

 
--使用示例
select * FROm dbo.F_Split(‘111,b2222,323232,32d,e,323232f,g3222‘, ‘,‘)

结果为

id          value

-------- ---------------------------------------

1           111

2           b2222

3           323232

4           32d

5           e

6           323232f

7           g3222

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

二、F_SplitLength：获取分割后的字符数组的长度

Create function [dbo].[F_SplitLength]
 (
 @String nvarchar(max), --要分割的字符串
 @Split nvarchar(10) --分隔符号
 )
 returns int
 as
 begin
 declare @location int
 declare @start int
 declare @length int
 
 set @String=ltrim(rtrim(@String))
 set @location=charindex(@split,@String)
 set @length=1
 while @location<>0
 begin
 set @start=@location+1
 set @location=charindex(@split,@String,@start)
 set @length=@length+1
 end
 return @length
 end

--调用示例
select dbo.F_SplitLength(‘111,b2222,323232,32d,e,323232f,g3222‘,‘,‘)

结果为7。

 

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

三、F_SplitOfIndex：获取分割后特定索引的字符串

Create function [dbo].[F_SplitOfIndex]
 (
 @String nvarchar(max), --要分割的字符串
 @split nvarchar(10), --分隔符号
 @index int --取第几个元素
 )
 returns nvarchar(1024)
 as
 begin
 declare @location int
 declare @start int
 declare @next int
 declare @seed int
 
 set @String=ltrim(rtrim(@String))
 set @start=1
 set @next=1
 set @seed=len(@split) 
 set @location=charindex(@split,@String)
 
 while @location<>0 and @index>@next
 begin
 set @start=@location+@seed
 set @location=charindex(@split,@String,@start)
 set @next=@next+1
 end
 if @location =0 select @location =len(@String)+1 
 return substring(@String,@start,@location-@start)
 end

--使用示例
select dbo.F_SplitOfIndex(‘111,b2222,323232,32d,e,323232f,g3222‘,‘,‘, 3)

SQL自定义函数split分隔字符串

标签：

热心网友 时间：2022-04-07 19:39

先建立一个自定义函数，之个函数非常有用，建议收入自已的数据库
CREATE FUNCTION mysplit--将以某分隔符分段的字串,按指定的顺序号提取子串：  

 (@str nvarchar(2000),--源字串
   @sn int,    --提取序号
   @Deli varchar(1) --分隔符
  )
   RETURNS varchar(100)
AS
BEGIN
declare @first int,@last int,@result varchar(1000),@sn0 int
select @sn0=0,@first=0,@LAST=1,@str=@str+REPLICATE(@DELI,1)
while @sn0!=@sn
 begin
 select @sn0=@sn0+1,@first=@LAST,@last=charindex(@DELI,@str,@LAST)+1
 end
if @last-@first-1<0
set @result=''
else
SET @RESULT=SUBSTRING(@str,@FIRST,@LAST-@FIRST-1)
RETURN ( @RESULT )
END

查询方法：

DECLARE @A VARCHAR(100),@B VARCHAR(100),@C VARCHAR(100)
SELECT 
@A=DBO.MYSPLIT('A|B|C',1,'|') ,
@B=DBO.MYSPLIT('A|B|C',2,'|') ,
@C=DBO.MYSPLIT('A|B|C',3,'|') 
SELECT @A,@B,@C

热心网友 时间：2022-04-07 20:57

自定义split函数

CREATE FUNCTION [dbo].[split]
(@str nvarchar(4000),@code varchar(10),@no int )
RETURNS varchar(200)
AS
BEGIN
declare @intLen int
declare @count int
declare @indexb int
declare @indexe int
set @intLen=len(@code)
set @count=0
set @indexb=1
if @no=0
if charindex(@code,@str,@indexb)<>0
return left(@str,charindex(@code,@str,@indexb)-1)
else
return @str
while charindex(@code,@str,@indexb)<>0
begin
set @count=@count+1
if @count=@no
break
set @indexb=@intLen+charindex(@code,@str,@indexb)
end
if @count=@no
begin
set @indexe=@intLen+charindex(@code,@str,@indexb)
if charindex(@code,@str,@indexe)<>0
return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))
else
return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1)
end
return ''
END

热心网友 时间：2022-04-07 22:32

itjob上有视频看