SQL 仅替换字符的第 n 个实例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8845961/
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
Replacing only the nth instance of character
提问by HL8
Is there a way a can replace the 1st instance of a character in a string with something eg.
有没有办法 a 可以用一些东西替换字符串中字符的第一个实例，例如。
^1402 WSN NIAMLAB^teertS htimS 005
to be
成为
&1402 WSN NIAMLAB^teertS htimS 005
keeping the second ^ in place
保持第二个 ^ 到位
回答by Phil Klein
To replace the first instance of a character I would recommend the use of the STUFFand CHARINDEXfunctions. STUFFinserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
要替换字符的第一个实例，我建议使用STUFF和CHARINDEX函数。 STUFF将一个字符串插入另一个字符串。它在起始位置删除第一个字符串中指定长度的字符，然后将第二个字符串插入到起始位置的第一个字符串中。
DECLARE @str varchar(100) = '^1402 WSN NIAMLAB^teertS htimS 005'
SELECT STUFF(@str, CHARINDEX('^', @str), 1, '&')
Note that you could also use STUFFin a query as follows:
请注意，您还可以STUFF按如下方式在查询中使用：
SELECT STUFF(<yourcolumn>, CHARINDEX('^', <yourcolumn>), 1, '&')
FROM <yourtable>
回答by Code Magician
Although the example in your question is how to replace only the 1st occurance of a char, your title indicates you want to replace the nth occurance which is a bit trickier.
尽管您问题中的示例是如何仅替换第 1 次出现的字符，但您的标题表明您要替换第 n 次出现，这有点棘手。
I wrote a function awhile back to find the nth occurance of a character in a string. You could use than to construct a string with SUBSTRING.
我写了一个函数来查找字符串中第 n 次出现的字符。您可以使用 than 来构造带有 SUBSTRING 的字符串。
if exists (
select 1
from dbo.sysobjects
where id = object_id(N'[dbo].[CHARINDEX3]')
and xtype in (N'FN', N'IF', N'TF'))
begin
drop function [dbo].[CHARINDEX3]
end
GO
/*
Example:
SELECT dbo.CHARINDEX3('a', 'abb abb a', 3)
SELECT dbo.CHARINDEX3('b', 'abb abb a', 5)
SELECT dbo.CHARINDEX3('a', 'abbabba', 3)
SELECT dbo.CHARINDEX3('b', 'abbabba', 5)
If @occurance > the max Occurrence, the function will return the max Occurrence
*/
CREATE FUNCTION dbo.CHARINDEX3
(
@TargetStr char(1),
@SearchedStr varchar(max),
@Occurrence int
)
RETURNS INT
AS
BEGIN
DECLARE @ret INT
; WITH
-- Tally table Gen Tally Rows: X2 X3
t1 AS (SELECT 1 N UNION ALL SELECT 1 N), -- 4 , 8
t2 AS (SELECT 1 N FROM t1 x, t1 y), -- 16 , 64
t3 AS (SELECT 1 N FROM t2 x, t2 y), -- 256 , 4096
t4 AS (SELECT 1 N FROM t3 x, t3 y), -- 65536 , 16,777,216
t5 AS (SELECT 1 N FROM t4 x, t4 y), -- 4,294,967,296, A lot
Numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Num
FROM t3 x, t3 y)
SELECT TOP 1 @ret = Num
FROM
(
SELECT Ident = ROW_NUMBER() OVER (ORDER BY N.Num), N.Num
FROM Numbers N
WHERE N.Num <= DATALENGTH(@SearchedStr)
AND SUBSTRING(@SearchedStr, N.Num, DATALENGTH(@TargetStr)) = @TargetStr
) R
WHERE Ident <= @Occurrence
ORDER BY Ident DESC
RETURN @ret
END
GO
Using that function you can get the location of the nth occurance and simply pull all the chars before and after the occurance, replacing the specific value.
使用该函数，您可以获得第 n 次出现的位置，并简单地提取出现前后的所有字符，替换特定值。
--Actual Code
DECLARE @loc INT
DECLARE @MyStr VARCHAR(200)
DECLARE @replacement CHAR(1)
SET @MySTr = '^1402 WSN NIAMLAB^teertS htimS 005'
SET @replacement = '$'
SELECT @loc = dbo.CHARINDEX3('^',@MyStr, 2)
SELECT SUBSTRING(@myStr, 1, @loc-1) + @replacement + SUBSTRING(@MyStr, @loc + 1, LEN(@MyStr)-@loc)
This example replaces just the 2nd ^with $
此示例仅将第二个替换^为$
回答by SQLMason
Untested code:
未经测试的代码：
SELECT STUFF('^1402 WSN NIAMLAB^teertS htimS 005', charindex('^', '^1402 WSN NIAMLAB^teertS htimS 005'), 1, '&');
GO
However, if the character doesn't exist, you would want to surround that with an if/then on charindex('^', '^1402 WSN NIAMLAB^teertS htimS 005') != 0
但是，如果该字符不存在，您可能希望用 if/then 将其包围 charindex('^', '^1402 WSN NIAMLAB^teertS htimS 005') != 0
