declare @str1 nvarchar(80)
set @str1=',2,3,3,4,2,5'
declare @str2 nvarchar(80)
set @str2=',1,2,2,3,3,4,2,5'如何判断@str1中的每一个值在@str2中存在重复,并返回第一个重复值,并返回第一个重复值的重复个数
set @str1=',2,3,3,4,2,5'
declare @str2 nvarchar(80)
set @str2=',1,2,2,3,3,4,2,5'如何判断@str1中的每一个值在@str2中存在重复,并返回第一个重复值,并返回第一个重复值的重复个数
解决方案 »
- SQL 月统计
- 这个触发器会出现死循环吗?
- 求写法,关于两个表之间的统计的
- 查询记录时报错:An error occurred while executing batch. Error message is: Non-negative number required.
- 中文查询遇到的问题,请看一下,谢谢!
- visual studio 2008 support for sql server 2008 英文版补丁无法安装
- 请教sql查询语句
- 跨服务器使用存储过程插入数据的使用使用到了事务!
- 连接SQL SERVER数据库问题
- 给已存在的表加一列,要在指定的位置加入,急~~~~~~~~~~~~~~~~~~
- sql游标快还是用WHILE循环快?
- 帮我看看这个SQL语句为什么老出错?
(
@sStr VARCHAR(5000),
@sLineSeparator VARCHAR(10), --行分隔符
@sFieldSeparator VARCHAR(10) --字段分隔符
)
RETURNS @tRtnVal TABLE
(
iden int identity(1, 1),
Field1 VARCHAR(1000),
Field2 VARCHAR(1000),
Field3 VARCHAR(1000),
Field4 VARCHAR(1000),
Field5 VARCHAR(1000)
)
WITH ENCRYPTION
AS
BEGIN
IF @sStr IS NULL OR (@sStr = '')
RETURN --默认行分隔符 ";'
IF LTRIM(RTRIM(ISNULL(@sLineSeparator, ''))) = ''
SELECT @sLineSeparator = ';'
--默认字段分隔符 ","
IF LTRIM(RTRIM(ISNULL(@sFieldSeparator, ''))) = ''
SELECT @sFieldSeparator = ',' DECLARE @ttmp TABLE (iNo INT IDENTITY(1, 1), stmp VARCHAR(5000), itmp INT)
DECLARE
@iIndex INT,
@stmp VARCHAR(5000) --分行
SELECT @iIndex = CHARINDEX(@sLineSeparator, @sStr)
WHILE @iIndex > 0
BEGIN
INSERT INTO @ttmp(stmp) VALUES(LEFT(@sStr, @iIndex - 1))
SELECT @sStr = SUBSTRING(@sStr, @iIndex + LEN(@sLineSeparator), 5000)
SET @iIndex = CHARINDEX(@sLineSeparator, @sStr)
END
IF LTRIM(RTRIM(@sStr)) <> ''
INSERT INTO @ttmp(stmp) VALUES(@sStr)
--分字段
DECLARE @iBeg INT,
@iEnd INT
SELECT @iBeg = 0, @iEnd = 0
SELECT @stmp = '' INSERT INTO @tRtnVal(Field1)
SELECT ma.stmp
FROM @ttmp ma UPDATE @tRtnVal
SET
Field2 = SUBSTRING(ma.Field1, CHARINDEX(@sFieldSeparator, ma.Field1) + LEN(@sFieldSeparator), 1000),
Field1 = SUBSTRING(ma.Field1, 0, CHARINDEX(@sFieldSeparator, ma.Field1))
FROM @tRtnVal ma
WHERE CHARINDEX(@sFieldSeparator, ma.Field1) > 0 UPDATE @tRtnVal
SET
Field3 = SUBSTRING(ma.Field2, CHARINDEX(@sFieldSeparator, ma.Field2) + LEN(@sFieldSeparator), 1000),
Field2 = SUBSTRING(ma.Field2, 0, CHARINDEX(@sFieldSeparator, ma.Field2))
FROM @tRtnVal ma
WHERE CHARINDEX(@sFieldSeparator, ma.Field2) > 0 UPDATE @tRtnVal
SET
Field4 = SUBSTRING(ma.Field3, CHARINDEX(@sFieldSeparator, ma.Field3) + LEN(@sFieldSeparator), 1000),
Field3 = SUBSTRING(ma.Field3, 0, CHARINDEX(@sFieldSeparator, ma.Field3))
FROM @tRtnVal ma
WHERE CHARINDEX(@sFieldSeparator, ma.Field3) > 0 UPDATE @tRtnVal
SET
Field5 = SUBSTRING(ma.Field4, CHARINDEX(@sFieldSeparator, ma.Field4) + LEN(@sFieldSeparator), 1000),
Field4 = SUBSTRING(ma.Field4, 0, CHARINDEX(@sFieldSeparator, ma.Field4))
FROM @tRtnVal ma
WHERE CHARINDEX(@sFieldSeparator, ma.Field4) > 0 RETURN
ENDgo
--实现
declare @str1 nvarchar(80)
set @str1=',2,3,3,4,2,5'
declare @str2 nvarchar(80)
set @str2=',1,2,2,3,3,4,2,5'select distinct ma.Field1
from dbo.fg_ConvertStringToTable(@str1, ',', ';') ma
where Field1 in (
select Field1
from dbo.fg_ConvertStringToTable(@str2, ',', ';') ma
group by Field1
having count(1) > 1
)
order by ma.Field1
set @str1=',2,3,3,4,2,5'
declare @str2 nvarchar(80)
set @str2=',1,2,2,3,3,4,2,5'declare @str nvarchar(80)
set @str=@str2
while (len(@str1)>0)
begin
select @str1=stuff(@str1,1,1,''),
@str=replace(@str,substring(@str1,1,1),'')
if len(@str)<len(@str2)-1
begin
print '字符串 str1 中第一个 '+rtrim(substring(@str1,1,1))+' 出现了 '+rtrim(len(@str2)-len(@str))+' 次!'
end
select @str1=stuff(@str1,1,1,''),@str2=@str
end/*测试结果字符串 str1 中第一个 2 出现了 3 次!
字符串 str1 中第一个 3 出现了 2 次!*/