问题描述
替换列中字符串的最佳方法是什么:
假设我有一个列类型为 varchar 的表,可能的值包含:
'sample text min(my value) 用 ) 和 ('
注意:我的价值可能会有所不同.
现在我想将其替换为:
min(max(my value))
所以在这种情况下的最终值是:
'sample text min(max(my value)) 用 ) 和 ('
我想对整个表执行更新.
是否可以使用纯 T-SQL?
所以 => 之前和转换之后的两个示例行:
<代码>1.值为:min(10).=>值为: min(max(10))2. 样本 min(cat) =>样本最小值(最大值(猫))
基本上用min(max(value))替换所有出现的min(value),其中'value'可以是任何字符串
首先你需要这个用户定义的函数来搜索用字符串替换模式:
CREATE FUNCTION dbo.PatternReplace(@InputString VARCHAR(4000),@Pattern VARCHAR(100),@ReplaceText VARCHAR(4000))返回 VARCHAR(4000)作为开始声明 @Result VARCHAR(4000) SET @Result = ''-- 匹配中的第一个字符声明@First INT-- 下一个开始搜索的字符声明 @Next INT SET @Next = 1-- 总字符串的长度 -- 如果@InputString 为 NULL,则为 8001声明@Len INT SET @Len = COALESCE(LEN(@InputString), 8001)-- 模式结束声明 @EndPattern INT而 (@Next <= @Len)开始SET @First = PATINDEX('%' + @Pattern + '%', SUBSTRING(@InputString, @Next, @Len))IF COALESCE(@First, 0) = 0 -- 不匹配 - 返回开始SET @Result = @Result +CASE --return NULL, 就像 REPLACE, 如果输入是 NULL当@InputString 为空时或 @Pattern 为空或 @ReplaceText 为 NULL THEN NULLELSE SUBSTRING(@InputString, @Next, @Len)结尾休息结尾别的开始-- 将匹配之前的字符连接到结果SET @Result = @Result + SUBSTRING(@InputString, @Next, @First - 1)SET @Next = @Next + @First - 1设置@EndPattern = 1-- 查找结束模式范围的开始而 PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0设置@EndPattern = @EndPattern + 1-- 查找模式范围的结尾而 PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) >0AND @Len >= (@Next + @EndPattern - 1)SET @EndPattern = @EndPattern + 1-- 在模式末尾或@Next + @EndPattern = @LenSET @Result = @Result + @ReplaceTextSET @Next = @Next + @EndPattern - 1结尾结尾返回(@结果)结尾
阅读更多
min(xxx) 出现不止一次:
最后,您可以简单地更新您的表格,如下所示:
更新你的表SET YourColumn = REPLACE(dbo.PatternReplace(YourColumn, '%min(', 'min(max('),'min(max(' + SUBSTRING(SUBSTRING(YourColumn,CHARINDEX('min(', YourColumn)+ 4,LEN(YourColumn)- CHARINDEX('min(',YourColumn)), 1,CHARINDEX(')', YourColumn)- ( CHARINDEX('min(', YourColumn) + 4 ))+ ')','min(max(' + SUBSTRING(SUBSTRING(YourColumn,CHARINDEX('min(', YourColumn)+ 4,LEN(YourColumn)- CHARINDEX('min(',YourColumn)), 1,CHARINDEX(')', YourColumn)- ( CHARINDEX('min(', YourColumn) + 4 ))+ '))');
What is the best way to replace string in column like:
Let's say I have table with column type varchar and with the possible value that contains:
'sample text min(my value) continue with sample text with ) and ('
Note: my value can vary.
Now I would like to replace it with:
min(max(my value))
so that the final value would in that case be:
'sample text min(max(my value)) continue with sample text with ) and ('
I would like to perform update on the whole table.
Is it possible using pure T-SQL?
So two sample rows before => and after transformation:
1. the value is: min(10). => the value is: min(max(10)) 2. sample min(cat) => sample min(max(cat))
Basically replace all occurrences of min(value) with min(max(value)) where 'value' can be any string
First of all you need this user defined function to search for replacing a pattern with string:
CREATE FUNCTION dbo.PatternReplace ( @InputString VARCHAR(4000), @Pattern VARCHAR(100), @ReplaceText VARCHAR(4000) ) RETURNS VARCHAR(4000) AS BEGIN DECLARE @Result VARCHAR(4000) SET @Result = '' -- First character in a match DECLARE @First INT -- Next character to start search on DECLARE @Next INT SET @Next = 1 -- Length of the total string -- 8001 if @InputString is NULL DECLARE @Len INT SET @Len = COALESCE(LEN(@InputString), 8001) -- End of a pattern DECLARE @EndPattern INT WHILE (@Next <= @Len) BEGIN SET @First = PATINDEX('%' + @Pattern + '%', SUBSTRING(@InputString, @Next, @Len)) IF COALESCE(@First, 0) = 0 --no match - return BEGIN SET @Result = @Result + CASE --return NULL, just like REPLACE, if inputs are NULL WHEN @InputString IS NULL OR @Pattern IS NULL OR @ReplaceText IS NULL THEN NULL ELSE SUBSTRING(@InputString, @Next, @Len) END BREAK END ELSE BEGIN -- Concatenate characters before the match to the result SET @Result = @Result + SUBSTRING(@InputString, @Next, @First - 1) SET @Next = @Next + @First - 1 SET @EndPattern = 1 -- Find start of end pattern range WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) = 0 SET @EndPattern = @EndPattern + 1 -- Find end of pattern range WHILE PATINDEX(@Pattern, SUBSTRING(@InputString, @Next, @EndPattern)) > 0 AND @Len >= (@Next + @EndPattern - 1) SET @EndPattern = @EndPattern + 1 --Either at the end of the pattern or @Next + @EndPattern = @Len SET @Result = @Result + @ReplaceText SET @Next = @Next + @EndPattern - 1 END END RETURN(@Result) END
Read more here
After creating this function you can try this:
DECLARE @x VARCHAR(max) SET @x = 'sample text min(my value) continue with sample text with ) and (' DECLARE @val VARCHAR(max) SET @val = SUBSTRING(SUBSTRING(@x,CHARINDEX('min(',@x)+4,LEN(@x)-CHARINDEX('min(',@x)),1,CHARINDEX(')',@x)-(CHARINDEX('min(',@x)+4)) SELECT REPLACE(dbo.PatternReplace(@x,'%min(','min(max('),'min(max('+@val+')','min(max('+@val+'))')
And you can see that the output is:
Occurrence of min(xxx) more than once:
Finally you can simply update your table as below:
UPDATE YourTable SET YourColumn = REPLACE(dbo.PatternReplace(YourColumn, '%min(', 'min(max('), 'min(max(' + SUBSTRING(SUBSTRING(YourColumn,CHARINDEX('min(', YourColumn)+ 4,LEN(YourColumn)- CHARINDEX('min(',YourColumn)), 1,CHARINDEX(')', YourColumn)- ( CHARINDEX('min(', YourColumn) + 4 ))+ ')', 'min(max(' + SUBSTRING(SUBSTRING(YourColumn,CHARINDEX('min(', YourColumn)+ 4,LEN(YourColumn)- CHARINDEX('min(',YourColumn)), 1,CHARINDEX(')', YourColumn)- ( CHARINDEX('min(', YourColumn) + 4 ))+ '))');