blocks|key|3657192|text|您真的需要在SQL+Server上使用T-SQL吗?第三代编程语言(如C%2B%2B、C#、VB.NET等)支持更强大的字符串操作方法；例如，您可以使用正则表达式，这些正则表达式不是在SQL+Server中本机实现的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3657193|您可以设计SQL+CLR函数或过程来解决此限制，但更合适的替代方法是使用客户端应用程序。还可以将SQL+Server+Integration+Services与脚本转换结合使用(可以使用C#或VB.NET进行转换)。|3657194|毫升|3657195|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Do you really need to do this on SQL Server, and use T-SQL? 3rd generation programming languages (e.g. C++, C#, VB.NET, etc.) support much more powerful string manipulation methods; for instance, you could use Regular Expressions, which are not implemented natively in SQL Server.

You could design a SQL CLR function or procedure to work around this limitation, but a much more appropriate alternative would be to use a client application. You could also use SQL Server Integration Services with a Script Transformation (you can use C# or VB.NET for that).

ML

blocks|key|2910446|text|您可以创建一个简单的CLR函数来为您执行regex操作。拥有可用于SQL查询的regex函数会非常有帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2910447|请看这篇文章：Regular+Expressions+Make+Pattern+Matching+And+Data+Extraction+Easier|offset|length|2910448|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/magazine/cc163473.aspx^0|0|7|1W|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You can create a simple CLR Function to do regex operations for you. Having regex functions available for SQL queries can be extremely helpful.

Take a look at this article: <a href="http://msdn.microsoft.com/en-us/magazine/cc163473.aspx" rel="nofollow noreferrer">Regular Expressions Make Pattern Matching And Data Extraction Easier</a>

blocks|key|2852685|text|假设您的列是varchar或类似类型(而不是text)，则可以使用T-SQL脚本进行替换。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2852686|您将需要迭代每个/REPLACE/+string，然后使用另一个嵌套循环来搜索下一个跟随数字。一旦你知道最后一个数字在哪里，你可以将这些数字转换成一个数字，并用该数字%2B+5000替换所需的字符串。|2852687|我给你贴了一个例子。您可以立即运行此脚本来测试它，但您需要根据您的表/列来调整它。|2852688|此外，如果列的类型是Text，也可以执行相同的操作，但需要将文本拆分为varchar块并迭代每个块。如果你需要帮助，请告诉我。|2852689|代码如下：|2852690|--+table+variable,+simulating+the+table+to+be+modified,+for+testing+purposes
--+all+references+to+@tbl+and+col+should+be+replaced+by+the+intended+table/column
declare+@tbl+table+(col+varchar(max))
insert+into+@tbl+values+('var+checkIn+=+moment(FIELD(/*REPLACE*/4,/*REPLACE*/9).GET());++
var+checkOut+=+moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET());++++
if+(checkIn+>+checkOut)+{++++++
++++FIELD(/*REPLACE*/4,/*REPLACE*/10).SET(checkIn.format("DD+MMM+YYYY"));++++++
++++checkOut+=+moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET());++
}++++
FIELD(/*REPLACE*/4,/*REPLACE*/11).SET(parseFloat(checkOut.diff(checkIn,+"days")).toFixed(2));')

declare+@col_txt+varchar(max),
++++++++@replace_at+int,+@digit_start+int,+@digit_end+int,+@number+int

select+@col_txt+=+col+from+@tbl+--+TODO+add+a+where+clause,+and+consider+using+a+cursor+for+each+row

set+@replace_at+=+0
--+loop+for+each+/*REPLACE*/+string
while+(1=1)+begin
++++set+@replace_at+=+charindex('/*REPLACE*/',+@col_txt,+@replace_at+%2B+1)
++++if+(@replace_at+=+0)+break

++++set+@digit_start+=+@replace_at+%2B+len('/*REPLACE*/')
++++set+@digit_end+=+@digit_start
++++--+loop+while+the+next+char+is+a+number,+and+save+its+position
++++while+(ascii(substring(@col_txt,+@digit_end,+1))+between+ascii(0)+and+ascii(9))+set+@digit_end+=+@digit_end+%2B+1
++++set+@number+=+cast(substring(@col_txt,+@digit_start,+@digit_end+-+@digit_start)+as+int)+%2B+5000
++++--+replace+current+/*REPLACE*/+and+number+with+the+found+number+%2B+5000+in+the+current+text+chunk
++++set+@col_txt+=+stuff(@col_txt,+@replace_at,+@digit_end+-+@replace_at,+@number)
end

--+update+the+original+table+with+the+value
update+@tbl+set+col+=+@col_txt+--+TODO+add+a+where+clause

--+only+for+test+purposes
select+col+from+@tbl|code-block|syntax|javascript|2852691|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|V|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Assuming your column is of a varchar or similar type (not text), you can preform that replacement with a T-SQL script.

You will need to iterate each /REPLACE/ string, then have another nested loop to search for the next folowing digits. Once you know where the last folowing digit is, you can convert those digits to a number, and replace the desired string with that number + 5000.

I'm posting you an example. You can run this script right away to test it, but you need to adapt it to you table/column.

Also, if your column is of the type Text, it is also possible to do the same, but it will require splitting the text into varchar chunks and iterating each chunk. Let me know if you need help with that.

Here is the code:

<pre><code>-- table variable, simulating the table to be modified, for testing purposes
-- all references to @tbl and col should be replaced by the intended table/column
declare @tbl table (col varchar(max))
insert into @tbl values ('var checkIn = moment(FIELD(/*REPLACE*/4,/*REPLACE*/9).GET()); 
var checkOut = moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET()); 
if (checkIn &gt; checkOut) { 
 FIELD(/*REPLACE*/4,/*REPLACE*/10).SET(checkIn.format("DD MMM YYYY")); 
 checkOut = moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET()); 
} 
FIELD(/*REPLACE*/4,/*REPLACE*/11).SET(parseFloat(checkOut.diff(checkIn, "days")).toFixed(2));')

declare @col_txt varchar(max),
 @replace_at int, @digit_start int, @digit_end int, @number int

select @col_txt = col from @tbl -- TODO add a where clause, and consider using a cursor for each row

set @replace_at = 0
-- loop for each /*REPLACE*/ string
while (1=1) begin
 set @replace_at = charindex('/*REPLACE*/', @col_txt, @replace_at + 1)
 if (@replace_at = 0) break

 set @digit_start = @replace_at + len('/*REPLACE*/')
 set @digit_end = @digit_start
 -- loop while the next char is a number, and save its position
 while (ascii(substring(@col_txt, @digit_end, 1)) between ascii(0) and ascii(9)) set @digit_end = @digit_end + 1
 set @number = cast(substring(@col_txt, @digit_start, @digit_end - @digit_start) as int) + 5000
 -- replace current /*REPLACE*/ and number with the found number + 5000 in the current text chunk
 set @col_txt = stuff(@col_txt, @replace_at, @digit_end - @replace_at, @number)
end

-- update the original table with the value
update @tbl set col = @col_txt -- TODO add a where clause

-- only for test purposes
select col from @tbl
</code></pre>

I am trying to replace text within one of my SQL columns. The text to replace can easily be found by finding all references of <code>/*REPLACE*/</code> with the next character being a numeric value which must be jacked up by 5000.

An example of the original value is displayed below.

<pre><code>var checkIn = moment(FIELD(/*REPLACE*/4,/*REPLACE*/9).GET()); 
var checkOut = moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET()); 
if (checkIn &gt; checkOut) { 
 FIELD(/*REPLACE*/4,/*REPLACE*/10).SET(checkIn.format("DD MMM YYYY")); 
 checkOut = moment(FIELD(/*REPLACE*/4,/*REPLACE*/10).GET()); 
} 
FIELD(/*REPLACE*/4,/*REPLACE*/11).SET(parseFloat(checkOut.diff(checkIn, "days")).toFixed(2));
</code></pre>

I need to find all references of <code>/*REPLACE*/</code> and increase the following character which is numeric by 5000.

An example of the new values is displayed below.

<pre><code>var checkIn = moment(FIELD(5004,5009).GET()); 
var checkOut = moment(FIELD(5004,5010).GET()); 
if (checkIn &gt; checkOut) { 
FIELD(5004,5010).SET(checkIn.format("DD MMM YYYY")); 
checkOut = moment(FIELD(5004,5010).GET()); 
} 
FIELD(5004,5011).SET(parseFloat(checkOut.diff(checkIn, "days")).toFixed(2));
</code></pre>

I have started with the basic but got lost with finding the numeric character after the text. 

<pre><code>SELECT column.REPLACE(column, '/*REPLACE*/',
</code></pre>

Any help please?

Replace text in value witout knowing the next character

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在尝试替换其中一个SQL列中的文本。通过查找/*REPLACE*/的所有引用，可以很容易地找到要替换的文本，其中下一个字符是一个数值，该数值必须向上提升5000。原始值的示例如下所示。var checkIn = moment(FIELD(/*REPLACE*/4,/*REPLACE*/9).GET());  var...

问将值中的文本替换为不知道下一个字符
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将值中的文本替换为不知道下一个字符EN