如何将userID1,userID2,userID3...转换成name1,name2,name3...

[wwfgu00ing]

一个表中userID是唯一的,其对应一个name

现如何将一个字符串str如何将userID1,userID2,userID3...转换成name1,name2,name3...
str的组成规则是多个userID用逗号隔开,可能为空,可能仅含有一个userID

如

userID name
111 name111
222 name222
333 name333

现str="111,222,333"

转换后的为"name111,name222,name333"

[水族杰纶]

[Quote=引用 21 楼 wwfgu00ing 的回复:]
引用 20 楼 roy_88 的回复:
引用 19 楼 wwfgu00ing 的回复:

是的 有一个不存在 就都返回'false'



SQL code


use Tempdb
go
--> -->

if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([userID] ……
[/Quote]
你要学会修改啊

[中国风]

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)

[中国风]

把臨時表替換為表名就行了

[NBDBA]

围观

这么麻烦不觉得设计有问题吗

[水族杰纶]

xml path

[中国风]

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
*/

[geniuswjt]

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 行受影响)

[中国风]

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
*/

[水族杰纶]

 
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)
              */

[wwfgu00ing]

userID与name之间并没有对应关系
转换的时候需要查找的

[wwfgu00ing]

只是这个例子比较特殊而已 楼上的都错了

[中国风]

[Quote=引用 6 楼 wwfgu00ing 的回复:]

userID与name之间并没有对应关系
转换的时候需要查找的
[/Quote]

列點特殊數據看看

[wwfgu00ing]

userID name
3048 wwf123
3049 mygood
30330 牛人盛典

[水族杰纶]

 
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)
*/  

[水族杰纶]

[Quote=引用 7 楼 wwfgu00ing 的回复:]
只是这个例子比较特殊而已 楼上的都错了
[/Quote]

那你把对的说出来

[wwfgu00ing]

不能根据userID替换成name啊
name要根据这个对应的userID在数据表中查找才可以

[水族杰纶]

[Quote=引用 12 楼 wwfgu00ing 的回复:]
不能根据userID替换成name啊
name要根据这个对应的userID在数据表中查找才可以
[/Quote]
我写两种都不是简单的替换吧
也是查找之后才操作的吧

[--小F--]

[Quote=引用 12 楼 wwfgu00ing 的回复:]
不能根据userID替换成name啊
name要根据这个对应的userID在数据表中查找才可以
[/Quote]
怎么个对应法？

[中国风]

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,牛人盛典
*/

[中国风]

用XML和replace替換字符不對麼?