替换 SQL 中的多个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22496761/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Replace multiple characters in SQL
提问by Nikhil Agrawal
I have a problem where I want to replace characters
我有一个问题，我想替换字符
I am using replacefunction but that is not giving desired output.
我正在使用replace函数，但这并没有给出所需的输出。
Values of column table_value needs to replaced with their fill names like
列 table_value 的值需要替换为其填充名称，例如
E - Email
P - Phone
M - Meeting
E - 电子邮件
P - 电话
M - 会议
I am using this query
我正在使用这个查询
select table_value,
replace(replace(replace(table_value, 'M', 'MEETING'), 'E', 'EMAIL'), 'P', 'PHONE') required_value
from foobar
so second required_valuerow should be EMAIL,PHONE,MEETINGand so on.
所以第二required_value行应该是EMAIL,PHONE,MEETING等等。
What should I do so that required value is correct?
我应该怎么做才能使所需的值正确？
回答by xdazz
The below will work (even it's not a smart solution).
下面将起作用（即使它不是一个聪明的解决方案）。
select
table_value,
replace(replace(replace(replace(table_value, 'M', 'MXXTING'), 'E', 'XMAIL'), 'P', 'PHONX'), 'X', 'E') required_value
from foobar
回答by Szymon
You can do it using CTE to split the table values into E, P and M, then replace and put back together.
您可以使用 CTE 将表值拆分为 E、P 和 M，然后替换并重新组合在一起。
I assumed each record has a unique identifer Idbut please replace that with whatever you have.
我假设每条记录都有一个唯一的标识符，Id但请用您拥有的任何标识符替换它。
;WITH cte
AS
(
SELECT Id, SUBSTRING(table_value, 1, 1) AS SingleValue, 1 AS ValueIndex
FROM replacetable
UNION ALL
SELECT replacetable.Id, SUBSTRING(replacetable.table_value, cte.ValueIndex + 1, 1) AS SingleValue, cte.ValueIndex + 1 AS ValueIndex
FROM cte
INNER JOIN replacetable ON cte.ValueIndex < LEN(replacetable.table_value)
)
SELECT DISTINCT Id,
STUFF((SELECT DISTINCT ','+ CASE SingleValue
WHEN 'E' THEN 'EMAIL'
WHEN 'P' THEN 'PHONE'
WHEN 'M' THEN 'MEETING'
END
FROM cte c
WHERE c.Id = cte.Id
AND SingleValue <> ','
FOR XML PATH ('')),1,1,'')
FROM cte
回答by toha
Sorry , for mess code, maybe this is not best way to solve this, but what I've tried:
抱歉，对于混乱的代码，也许这不是解决此问题的最佳方法，但我已尝试过：
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE Function [dbo].[fn_CSVToTable]
(
@CSVList Varchar(max)
)
RETURNS @Table TABLE (ColumnData VARCHAR(100))
AS
BEGIN
DECLARE @S varchar(max),
@Split char(1),
@X xml
SELECT @Split = ','
SELECT @X = CONVERT(xml,' <root> <s>' + REPLACE(@CSVList,@Split,'</s> <s>') + '</s> </root> ')
INSERT INTO @Table
SELECT CASE RTRIM(LTRIM(T.c.value('.','varchar(20)'))) WHEN 'M' THEN 'Meeting'
WHEN 'P' THEN 'Phone'
WHEN 'E' THEN 'Email'
End
FROM @X.nodes('/root/s') T(c)
RETURN
END
GO
Then When I run this:
然后当我运行这个：
Select Main.table_value,
Left(Main.ColumnData,Len(Main.ColumnData)-1) As ColumnData
From
(
Select distinct tt2.table_value,
(
Select tt1.ColumnData+ ',' AS [text()]
From (
SELECT
*
FROM dbo.TestTable tt
CROSS APPLY dbo.fn_CSVToTable(tt.table_value)
) tt1
Where tt1.table_value = tt2.TABLE_value
ORDER BY tt1.table_value
For XML PATH ('')
) ColumnData
From dbo.TestTable tt2
) [Main]
I get this:
我明白了：
table_value ColumnData
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
E,P Email,Phone
E,P,M Email,Phone,Meeting
P,E Phone,Email
P,M Phone,Meeting
(4 row(s) affected)
回答by Wendy
You could also use a DECODE or CASE to translate the values.
您还可以使用 DECODE 或 CASE 来转换值。
