字符串提取
在一个字段里,有如下记录 '001,002,003,004 ',现有一个字符串 '004,002 ',我想用一个存储过程,将 '001,002,003,004 '记录中与字符串 '004,002 '不匹配的剩余字符串取出来,也就是想比较一下,将结果显示为 '001,003 ',不知该如何写这个存储过程?
[解决办法]
declare @a varchar(1000)
declare @b varchar(1000)
declare @tmp varchar(1000)
set @a= '001,002,003,004 '
set @b= '002,004 '
select @tmp=left(@b,charindex( ', ',@b)-1)
set @a=replace(replace(@a, ', '+@tmp, ' '),@tmp+ ', ', ' ')
set @tmp=right(@b,charindex( ', ',@b)-1)
set @a=replace(replace(@a, ', '+@tmp, ' '),@tmp+ ', ', ' ')
select @a
[解决办法]
create proc Sp_Differ
@c varchar(1000),
@d varchar(1000)
as
declare @a varchar(1000)
declare @b varchar(1000)
declare @tmp varchar(1000)
set @a=@c
set @b=@d
select @tmp=left(@b,charindex( ', ',@b)-1)
set @a=replace(replace(@a, ', '+@tmp, ' '),@tmp+ ', ', ' ')
set @tmp=right(@b,charindex( ', ',@b)-1)
set @a=replace(replace(@a, ', '+@tmp, ' '),@tmp+ ', ', ' ')
select @a
go
--示例
Sp_Differ '001,002,003,004 ', '002,004 '
drop proc Sp_Differ
--结果
001,003
[解决办法]
--樓上的有點問題的...
--try: Sp_Differ '001,002,003,004 ', '002,004,003 ' 結果:001,004
--試試看:
create proc dbo.usp_test
@str1 varchar(100),
@str2 varchar(50)
AS
create table t1(id int identity(1,1),str1 varchar(10))
declare @sql varchar(1000)
set @sql= 'insert into t1(str1) select ' ' '+replace(@str1, ', ', ' ' ' union all select ' ' ')+ ' ' ' '
set @sql=@sql+ ' delete t1 where str1 in ( ' ' '+replace(@str2, ', ', ' ' ', ' ' ')+ ' ' ') '
exec(@sql)
declare @str varchar(100)
set @str = ' '
select @str=@str+ ', '+str1 from t1 order by id
if len(@str)> 0
select stuff(@str,1,1, ' ')
drop table t1
GO
exec dbo.usp_test '001,002,003,004 ', '004,002 '
drop proc usp_test
------------------
001,003
[解决办法]
--建立测试数据
CREATE TABLE TEST(ID INT,NAME VARCHAR(10),PartNumber VARCHAR(50))
INSERT TEST
SELECT 1, 'A ', '001,002,003,004 ' UNION ALL
SELECT 2, 'B ', '005,006,007,008 '
CREATE TABLE [TEMP]([ID] INT,[NAME] VARCHAR(10),PartNumber VARCHAR(50))
GO
--写一个聚合函数：
CREATE FUNCTION dbo.fn_Merge(@F1 VARCHAR(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @r VARCHAR(8000)
SET @r= ' '
SELECT @r=@r+ ', '+PartNumber FROM [TEMP] WHERE [NAME]=@F1
RETURN STUFF(@r,1,1, ' ')
END
GO
CREATE PROC P_test
@STR VARCHAR(50)
AS
--生成临时表
SELECT TOP 8000 [ID] = IDENTITY(INT, 1, 1) INTO # FROM syscolumns a, syscolumns b
--分拆处理
INSERT [TEMP]([ID],[NAME],PartNumber)
SELECT A.[ID], [Name], 'PartNumber '=SUBSTRING(A.PartNumber, B.[ID], CHARINDEX( ', ', A.PartNumber + ', ', B.[ID]) - B.[ID])
FROM TEST A, # B
WHERE SUBSTRING( ', ' + A.PartNumber, B.[ID], 1) = ', '
--删除相同字符
DELETE [TEMP]
WHERE CHARINDEX(PartNumber,@STR)> 0
--调用函数
SELECT [NAME], dbo.fn_Merge([NAME]) AS PartNumber
FROM [TEMP]
GROUP BY [NAME]
GO
--执行存储过程
EXEC P_test '003,004,005,006 '
--删除测试环境
DROP PROC P_test
DROP FUNCTION fn_Merge
DROP TABLE TEST,[TEMP]
/*
NAMEPartNumber
A001,002
B007,008
*/
