求一函數

[bb_chen]

有字符串 a;b1;c23;d34 ,其中;為分割符。
現要實現功能：根據傳入的數值來刪除對應的項，如1則結果為　b1;c23;d34，如2則結果為a;c23;d34，如3則結果為a;b1;d34，如4則結果為a;b1;c23。
不知表達清楚沒有，謝謝！

[--小F--]

清楚了 等楼下的字符截取函数

[SQL77]

等函数

[SQL77]

不过在程序中更方便

[华夏小卒]

 -----只适合有4个部分（3个分号）的情况

if object_id('f_str')is not null drop function f_str
go
create function f_str(@s varchar(50),@name int)
returns varchar(30)
as
begin
	set @s=replace(@s,';','.')
	set @s=case @name when 4 then replace(@s,'.'+parsename(@s,1),'')
					  when 3 then replace(@s,parsename(@s,2)+'.','')
					  when 2 then replace(@s,parsename(@s,3)+'.','')
					  when 1 then replace(@s,parsename(@s,4)+'.' ,'')	
			end
	return replace(@s,'.',';')
end

go
-------------------------------------------
select s=dbo.f_str('a;b1;c23;d34',1)
 
b1;c23;d34

(1 行受影响)
-------------------------------------------
select s=dbo.f_str('a;b1;c23;d34',2)

a;c23;d34

(1 行受影响)


-------------------------------------------
select s=dbo.f_str('a;b1;c23;d34',3)

a;b1;d34

(1 行受影响)
-------------------------------------------
select s=dbo.f_str('a;b1;c23;d34',4)


a;b1;c23

(1 行受影响)

[rucypli]

不错

[navy887]

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

--       Author : navy887(草根)

--       用途：分隔字符串的存储过程

--       使用方法：EXEC DivideString 'a;b1;c23;d34',';','表名'

--       转载请注明出处

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

CREATE PROCEDURE DivideString
(
@String NVARCHAR(1210),  -- 要分隔的字符串
@SPLITCHAR NVARCHAR(10) = ';', -- 默认分隔字符
@TableName NVARCHAR(30) = 'table'  --默认表名
)
AS
DECLARE @L INT -- 第一个分隔字符的位置
DECLARE @S INT -- 第二个分隔字符的位置
SET @L = 0 
SET @S = CHARINDEX(@SPLITCHAR, @String, @L)

WHILE @L <= LEN(@String)
BEGIN
 DECLARE @ColName NVARCHAR(50) 
 IF @S = 0 SET @S = LEN(@String) + 1 -- 如果到最后一个字符串那么第二个分隔字符的位置就是这个字符串的长度加一
 SET @ColName = SUBSTRING(@String, @L, @S - @L) -- 取值
 SET @L = @S + 1
 SET @S = CHARINDEX(@SPLITCHAR, @String, @L)
 IF LTRIM(RTRIM(@ColName)) = '' CONTINUE -- 如果是空字符串就跳过
 DECLARE @SQL NVARCHAR(1000)
 SET @SQL ='INSERT INTO ' + @TableName + '(tcname) select ''' +@ColName+'''' --插入表的语句，根据自己需求修改
 EXEC (@SQL)
 --Print @sql
END

[shhzyy]

create FUNCTION [dbo].[UF_CombineString]
(
	@expression varchar(8000),		--要被分解的字符串
	@splitchar char(1),				--分割字符
	@index int,						--第N位，从一开始
	@value varchar(4000)			--要设置的字符
)
RETURNS varchar(8000)
AS
	BEGIN
	
	if right(@expression,1)<>@splitchar set @expression = @expression+@splitchar

	DECLARE @BeginIndex int 
	DECLARE @EndIndex int 
	DECLARE @Pos int
	DECLARE @i int
	SET @i=1
	set @Pos = 0
	set @BeginIndex=0

	WHILE @i<@index
	BEGIN
		SET @Pos = CHARINDEX(@splitchar,@expression,@BeginIndex+1)
		IF @Pos = 0 
		begin
			SET @expression = @expression + @splitchar
			set @BeginIndex = @BeginIndex + 1
		end
		ELSE
			SET @BeginIndex = @Pos
			
		SET @i = @i + 1		
	END

	SET @EndIndex = charindex(@splitchar,@expression,@BeginIndex+1)		
	if @EndIndex = 0 SET @EndIndex = @BeginIndex
		
	return substring(@expression,1,@BeginIndex)+@value+substring(@expression,@EndIndex+1,9999)
	
	END

用法：
select dbo.UF_CombineString(字段名,';',第几位,'') from dbo.CM_Client

[shhzyy]

select dbo.UF_CombineString(字段名,';',第几位,'') from 表名

[快乐_石头]

-->Title:Generating test data
-->Author:happy_stone【不會飛的石頭】
-->Date :2009-10-21 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_SetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_SetStr]
GO
--分段截取函数
CREATE FUNCTION dbo.f_SetStr(
@s varchar(8000),      --包含数据项的字符串
@pos int,             --要更新的数据项的段
@split varchar(10)     --数据分隔符
)RETURNS varchar(8000)
AS
BEGIN
	DECLARE @splitlen int,@p1 int,@p2 int
	SELECT @splitlen=LEN(@split+'a')-2,
		@p1=1,
		@p2=CHARINDEX(@split,@s+@split)
	WHILE @pos>1 AND @p1<=@p2
		SELECT @pos=@pos-1,
			@p1=@p2+@splitlen+1,
			@p2=CHARINDEX(@split,@s+@split,@p1)
	RETURN(CASE
		WHEN @p1<@p2 THEN STUFF(@s,@p1,@p2-@p1+1,'')
		WHEN @p2>LEN(@s) THEN @s+''
		WHEN @p2=@p1 THEN STUFF(@s,@p1,0,'') 
		ELSE @s END)
END
GO
select [dbo].[f_SetStr]('a;b1;c23;d34',2,';')
select [dbo].[f_SetStr]('a;b1;c23;d34',3,';')
/*
-------------------
a;c23;d34
-------------------
a;b1;d34

*/

[fhjzgsy]

很好，顶顶

[gsk09]

create function dbo.stuffStr(@str varchar(max),@i smallint)
	returns varchar(max)
as
begin
if (@i=0 or (len(@str)-len(replace(@str,';','')))< @i) return @str
declare @startIdx smallint
set @startIdx=0
while @i>1	select @startIdx=charindex(';',@str,@startIdx+1),@i=@i-1
return stuff(@str,@startIdx+1,(charindex(';',@str,@startIdx+1)-@startIdx),'')
end

select dbo.stuffstr('0asdf0;1fdf881;2fewe2',0)
----------------------------------------
0asdf0;1fdf881;2fewe2


select dbo.stuffstr('0asdf0;1fdf881;2fewe2',1)
----------------------------------------
1fdf881;2fewe2


select dbo.stuffstr('0asdf0;1fdf881;2fewe2',2)
----------------------------------------
0asdf0;2fewe2


select dbo.stuffstr('0asdf0;1fdf881;2fewe2',3)
----------------------------------------
0asdf0;1fdf881;2fewe2

[wldzjj]

function test($cou,$b,$k)
{
$l = '';
for($i=0;$i<$cou;$i++)
{
if($i == $k-1)
{
echo '';
}
else
{
echo $b[$i].';';
}
}

}

$a='a;b1;c23;d34';
$b = explode(';',$a);
$m = test(count($b),$b,2);
echo $m;

[dawugui]

[Quote=引用楼主 bb_chen 的回复:]
有字符串 a;b1;c23;d34  ,其中;為分割符。
現要實現功能：根據傳入的數值來刪除對應的項，如1則結果為　b1;c23;d34，如2則結果為a;c23;d34，如3則結果為a;b1;d34，如4則結果為a;b1;c23。
不知表達清楚沒有，謝謝！
[/Quote]如果只有四个数据,用四楼的最好.

[mugua604]

CREATE FUNCTION dbo.f_SetStr(
@st varchar(8000),      --包含数据项的字符串
@a1 int,             --要更新的数据项的段
@s  varchar(10)     --数据分隔符
)RETURNS varchar(8000)
as 
BEGIN 
declare @st1 varchar(8000), @i int,@a INT
set @i =1
set @a =0
set @st1 = @st
while (@i!=0)
	BEGIN
		set @a =@a+1
	    SELECT @i=charindex(@s,@st)
		SET @st = right(@st,len(@st)-@i)
		if(@a=@a1-1)
			BEGIN
			SET @i=charindex(';',@st)
			SET @st = left(@st,@i)
			set @st1 = replace(@st1,@st,'')
	        END
	END
 RETURN @st1
END

SELECT  dbo.f_SetStr('a;b1;c23;d34;DSFS;SDFSDF;TYUIGHSFD;FD',5,';')
/*

-------------------------------
a;b1;c23;d34;SDFSDF;TYUIGHSFD;FD

(1 行受影响)

*/

[bancxc]

[code=SQL]

---函数不会写 写个存储过程
if object_ID('GetStr') Is not null
drop Procedure GetStr
go
Create Procedure GetStr(@str NVARCHAR(20) output,@n int)
as
begin
declare @delStr AS NVARCHAR(100)
DECLARE @X AS XML
Select @X='<root><v>'+replace(@STR,';','</v><v>')+'</v></root>'
select @delStr =N'set @myxml.modify(''delete /root/v['+ cast(@n as nvarchar(10))+']'')'
exec sp_executesql @delStr,N'@myxml xml output',@X OUTPUT
select @STR= replace(replace(replace(cast(@x as nvarchar(100)),'<root><v>',''),'</v></root>',''),'</v><v>',';')
select @STR
end
go

Exec GetStr 'a;b1;c23;d34',1
Exec GetStr 'a;b1;c23;d34',2
Exec GetStr 'a;b1;c23;d34',3
Exec GetStr 'a;b1;c23;d34',4
/*

--------------------
b1;c23;d34

(1 行受影响)


--------------------
a;c23;d34

(1 行受影响)


--------------------
a;b1;d34

(1 行受影响)


--------------------
a;b1;c23

(1 行受影响)

*/
[/CODE]

[DengXingJie]

幫頂

[feixianxxx]

create   function ko_ok (@s varchar(100),@N int,@fi varchar(10))
returns varchar(100)
as
begin
declare @sj varchar(100) 
select @sj=substring(@s,n,charindex(@fi,@s+@fi,n)-n)
from num 
where n<=len(@s) and substring(@fi+@s,n,1)=@fi and n-len(replace(left(@s,n),@fi,''))+1=@n 
return @sj

end
SELECT  dbo.ko_ok('a;b1;c23;d34',4,';')
/*
----------------------------------------------------------------------------------------------------
d34

*/
SELECT  dbo.ko_ok('a;b1;c23;d34',3,';')
/*

----------------------------------------------------------------------------------------------------
c23
*/

[feixianxxx]

alter   function ko_ok (@s varchar(100),@N int,@fi varchar(10))
returns varchar(100)
as
begin
declare @sj varchar(100) 
select @sj=substring(@s,number,charindex(@fi,@s+@fi,number)-number)
from master..spt_values
where type='p' and number<=len(@s) and substring(@fi+@s,number,1)=@fi and number-len(replace(left(@s,number),@fi,''))+1=@n 
select @sj=case when @n<>1 then  replace(@s,';'+@sj,'')
else  replace(@s,@sj,'') end
return @sj

end
SELECT  dbo.ko_ok('a;b1;c23;d34',4,';')
/*
----------------------------------------------------------------------------------------------------
a;b1;c23

*/
SELECT  dbo.ko_ok('a;b1;c23;d34',3,';')
/*

----------------------------------------------------------------------------------------------------
a;b1;d34
*/

[小宏]

create function test_str
(@str varchar(100) , @spl varchar(5) , @num int)
returns varchar(100)
as
begin
declare @i int
declare @result varchar(100)
declare @ck int
declare @pre int
set @pre =1
set @ck = 1
set @i = charindex(@spl , @str)
while @i >0
begin
if (abs(@ck - @num) = 0)
	begin
		set @result = stuff(@str , @pre , @i , '')
		break;
	end
else
	begin
		set @pre = @i
		set @i = charindex(@spl , @str)
		set @ck = @ck + 1
	end

end
return @result
end

select s=dbo.test_str('a;b1;c23;d34',';' ,4)

---------------------------------------
s
a1;c23;d34

[小宏]

create function test_str
(@str varchar(100) , @spl varchar(5) , @num int)
returns varchar(100)
as
begin
declare @i int
declare @result varchar(100)
declare @ck int
declare @pre int
set @pre =1
set @ck = 1
set @i = charindex(@spl , @str)
while @i >0
begin
if (abs(@ck - @num) = 0)
	begin
		set @result = stuff(@str , @pre , @i-@pre , '')
		break;
	end
else
	begin
		set @pre = @i
		set @i = charindex(@spl , @str , @pre+1)
		set @ck = @ck + 1
	end

end
return @result
end

select s=dbo.test_str('a;b1;c23;d34',';' ,3)
select s=dbo.test_str('a;b1;c23;d34',';' ,2)

---------------------------------------
s
a;b1;d34
a;c23;d34

不好意思刚写错了。。。