27,579
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([userID] int,[name] nvarchar(6))
Insert #
select 3048,N'wwf123' union all
select 3049,N'mygood' union all
select 30330,N'牛人盛典'
Go
DECLARE @str NVARCHAR(1000)
SET @str='3048,30330'
SET @str=','+@str+','
Select @str=REPLACE(@str,','+RTRIM([userID])+',',','+[name]+',') from # WHERE ','+@str+',' LIKE '%,'+RTRIM([userID])+',%'
SELECT SUBSTRING(@str,2,LEN(@str)-2) AS STR
/*
wwf123,牛人盛典
*/
if not object_id('tb') is null
drop table tb
Go
Create table tb([userID] int,[name] nvarchar(7))
Insert tb
select 111,N'name111' union all
select 222,N'name222' union all
select 333,N'name333'
Go
DECLARE @str VARCHAR(100)
SET @str='111,222,333'
WHILE EXISTS(SELECT 1
FROM dbo.tb
WHERE CHARINDEX(','+LTRIM([userID])+',',','+@str+',')>0)
BEGIN
SELECT @str=REPLACE(@str,[UserId],[name])
FROM tb
END
SELECT @str
/*
name111,name222,name333
(1 row(s) affected)
*/
if not object_id('tb') is null
drop table tb
Go
Create table tb([userID] int,[name] nvarchar(7))
Insert tb
select 111,N'name111' union all
select 222,N'name222' union all
select 333,N'name333'
Go
DECLARE @str VARCHAR(100)
SET @str='111,222,333'
SELECT STUFF((SELECT ','+[name]
FROM dbo.tb
WHERE CHARINDEX(','+LTRIM([userID])+',',','+@str+',')>0
FOR XML PATH('')),1,1,'')
/*
name111,name222,name333
(1 row(s) affected)
*/
CHARINDEX/PATINDEX
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([userID] int,[name] nvarchar(7))
Insert #
select 111,N'name111' union all
select 222,N'name222' union all
select 333,N'name333'
Go
DECLARE @str NVARCHAR(1000)
SET @str='111,222,333'
Select * from # WHERE PATINDEX( '%,'+RTRIM([userID])+',%', ','+@str+',')>0
/*
userID name
111 name111
222 name222
333 name333
*/
declare @str varchar(8000)='111,222,333'
if isnull(@str,'')<>''
set @str=stuff(replace(@str,',',',name'),1,0,'name')
select @str
/*
----------------------------
name111,name222,name333
(1 行受影响)
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([userID] int,[name] nvarchar(7))
Insert #
select 111,N'name111' union all
select 222,N'name222' union all
select 333,N'name333'
Go
DECLARE @str NVARCHAR(1000)
SET @str='111,222,333'
Select * from # WHERE ','+@str+',' LIKE '%,'+RTRIM([userID])+',%'
/*
userID name
111 name111
222 name222
333 name333
*/
use Tempdb
go
--> -->
if not object_id(N'T') is null
drop table T
Go
Create table T([userID] int,[name] nvarchar(6))
Insert T
select 3048,N'wwf123' union all
select 3049,N'mygood' union all
select 30330,N'牛人盛典'
Go
CREATE FUNCTION fn_Str(@str NVARCHAR(1000))
RETURNS NVARCHAR(1000)
AS
begin
if EXISTS(SELECT * FROM (
SELECT Col=SUBSTRING(@str,number,CHARINDEX(',',@str+',',number)-number)
FROM master.dbo.spt_values
WHERE type='P' AND CHARINDEX(',',','+@str,number)=number
) AS a
WHERE NOT EXISTS(SELECT 1 FROM T WHERE userID=a.Col)
)
RETURN 'False'
SET @str=','+@str+','
Select @str=REPLACE(@str,','+RTRIM([userID])+',',','+[name]+',') from T WHERE ','+@str+',' LIKE '%,'+RTRIM([userID])+',%'
RETURN( SUBSTRING(@str,2,LEN(@str)-2) )
END
GO
DECLARE @str NVARCHAR(1000)
SET @str='3048,30339'
SELECT dbo.fn_str(@str)