这样吗:
DECLARE @cxtj VARCHAR(2000)
SET @cxtj='and yhy=1112 AND lszh NOT IN(1,2,3,4,5) and hwh=9'select stuff(@cxtj,charindex('AND lszh NOT IN(',@cxtj),
CHARINDEX(')',@cxtj,charindex('AND lszh NOT IN(',@cxtj)) - charindex('AND lszh NOT IN(',@cxtj)+1,
'')
/*
and yhy=1112 and hwh=9
*/
DECLARE @cxtj VARCHAR(2000)
SET @cxtj='and yhy=1112 AND lszh NOT IN(1,2,3,4,5) and hwh=9'select stuff(@cxtj,charindex('AND lszh NOT IN(',@cxtj),
CHARINDEX(')',@cxtj,charindex('AND lszh NOT IN(',@cxtj)) - charindex('AND lszh NOT IN(',@cxtj)+1,
'')
/*
and yhy=1112 and hwh=9
*/
DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' AND lszh NOT IN(1,2) and hwh=9'
DECLARE @xzjl VARCHAR(400)
SET @xzjl='1,2,3,4,5'
--结果: and hwh=9这样就不行了吧?能不能通用点?
DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' and hwh=9'
DECLARE @xzjl VARCHAR(400)
SET @xzjl='1,2,3,4,5'
--结果: and hwh=9不好意思,是这种情况。可以写if else的,谢谢,随便你怎么写
DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' AND lszh NOT IN(1,2) and hwh=9'
DECLARE @xzjl VARCHAR(400)
SET @xzjl='1,2,3,4,5'
--结果: and hwh=9这样就不行了吧?能不能通用点?好像也可以:
DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' AND lszh NOT IN(1,2) and hwh=9'--结果: and hwh=9
select stuff(@cxtj,charindex('AND lszh NOT IN(',@cxtj),
CHARINDEX(')',@cxtj,charindex('AND lszh NOT IN(',@cxtj)) - charindex('AND lszh NOT IN(',@cxtj)+1,
'')
/*
and hwh=9
*/
DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' and hwh=9'
DECLARE @xzjl VARCHAR(400)
SET @xzjl='1,2,3,4,5'
--结果: and hwh=9不好意思,是这种情况。可以写if else的,谢谢,随便你怎么写这样吗:DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' and hwh=9'
DECLARE @xzjl VARCHAR(400)
--结果: and hwh=9
select case when charindex('AND lszh NOT IN(',@cxtj) >0 then
stuff(@cxtj,charindex('AND lszh NOT IN(',@cxtj),
CHARINDEX(')',@cxtj,charindex('AND lszh NOT IN(',@cxtj)) - charindex('AND lszh NOT IN(',@cxtj)+1,
'')
else @cxtj
end
/*
and hwh=9
*/
DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' and hwh=9'
DECLARE @xzjl VARCHAR(400)
SET @xzjl='1,2,3,4,5'
--结果: and hwh=9不好意思,是这种情况。可以写if else的,谢谢,随便你怎么写这样吗:DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' and hwh=9'
DECLARE @xzjl VARCHAR(400)
--结果: and hwh=9
select case when charindex('AND lszh NOT IN(',@cxtj) >0 then
stuff(@cxtj,charindex('AND lszh NOT IN(',@cxtj),
CHARINDEX(')',@cxtj,charindex('AND lszh NOT IN(',@cxtj)) - charindex('AND lszh NOT IN(',@cxtj)+1,
'')
else @cxtj
end
/*
and hwh=9
*/STUFF ( character_expression , start , length ,character_expression )
以下示例在第一个字符串 abcdef 中删除从第 2 个位置(字符 b)开始的三个字符,然后在删除的起始位置插入第二个字符串,从而创建并返回一个字符串。 复制代码
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO
下面是结果集: 复制代码
---------
aijklmnef (1 row(s) affected)
我看了帮助文档里面的,才有四个参数嘛,求解释啊。你的答案已经可以了,只是我看不懂,哈哈,谢谢
DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' and hwh=9'
DECLARE @xzjl VARCHAR(400)
SET @xzjl='1,2,3,4,5'
--结果: and hwh=9不好意思,是这种情况。可以写if else的,谢谢,随便你怎么写这样吗:DECLARE @cxtj VARCHAR(2000)
SET @cxtj=' and hwh=9'
DECLARE @xzjl VARCHAR(400)
--结果: and hwh=9
select case when charindex('AND lszh NOT IN(',@cxtj) >0 then
stuff(@cxtj,charindex('AND lszh NOT IN(',@cxtj),
CHARINDEX(')',@cxtj,charindex('AND lszh NOT IN(',@cxtj)) - charindex('AND lszh NOT IN(',@cxtj)+1,
'')
else @cxtj
end
/*
and hwh=9
*/STUFF ( character_expression , start , length ,character_expression )
以下示例在第一个字符串 abcdef 中删除从第 2 个位置(字符 b)开始的三个字符,然后在删除的起始位置插入第二个字符串,从而创建并返回一个字符串。 复制代码
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO
下面是结果集: 复制代码
---------
aijklmnef (1 row(s) affected)
我看了帮助文档里面的,才有四个参数嘛,求解释啊。你的答案已经可以了,只是我看不懂,哈哈,谢谢哦,就是stuff是个填充函数,SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO
就是值从第二字符开始,取3个字符,也就是bcd,然后把这个bcd填充为 ijklmn,于是结果为 aijklmnef